如何为含CASE WHEN的查询添加COUNT统计（适配水晶报表）

Got it, let's tackle this problem step by step. You want to add summary counts for your NewBorn and DayCase columns while keeping all existing data from your query, and this needs to work with Crystal Reports. Here are two straightforward, effective approaches:

1. Handle Counts Directly in Your SQL Query #

First, let's clean up your original query (those nested subqueries for NewBorn and DayCase aren't necessary) and add the summary counts using window functions. This way, the counts will be included in every row of your result set, making them easy to use in Crystal Reports.

SELECT 
    ELG, 
    UHID, 
    BED, 
    DIAGNOSIS, 
    Specialisation, 
    AdmitDate, 
    LOS, 
    DischargeDate, 
    REMARKS, 
    Name, 
    -- Simplified NewBorn calculation
    CASE 
        WHEN BedName LIKE 'INCUBATOR%' OR BedName LIKE 'NS%' THEN '1' 
        ELSE CAST(WardID AS VARCHAR(20)) 
    END AS NewBorn,
    -- Simplified DayCase calculation
    CASE 
        WHEN LOS <= 1 THEN '1' 
        ELSE CAST(LOS AS VARCHAR(20)) 
    END AS DayCase,
    -- Total count of NewBorn entries marked as '1'
    SUM(CASE WHEN BedName LIKE 'INCUBATOR%' OR BedName LIKE 'NS%' THEN 1 ELSE 0 END) OVER() AS TotalNewBorn,
    -- Total count of DayCase entries marked as '1'
    SUM(CASE WHEN LOS <= 1 THEN 1 ELSE 0 END) OVER() AS TotalDayCase
FROM DischargeWard

The OVER() clause without any partitioning tells SQL to calculate the sum across the entire result set, so every row will show the same total counts for TotalNewBorn and TotalDayCase. You can then drag these columns into your Crystal Report (e.g., in a header or footer) to display the totals.

2. Calculate Counts Within Crystal Reports (No SQL Changes) #

If you prefer not to modify your base query, Crystal Reports has built-in tools to compute these summaries directly in the report designer:

• For NewBorn count:
  1. Right-click in your report design area and select Insert > Summary.
  2. Choose the NewBorn field from your dataset.
  3. Set the summary function to Count, then click Show Formula.
  4. In the formula editor, enter: {NewBorn} = '1' (use {NewBorn} = 1 if the value is numeric instead of string).
  5. Select where to place the summary (e.g., Report Footer to show it once at the end).
• Repeat for DayCase:
  Use the same steps, but set the formula to {DayCase} = '1' (adjust for numeric/string type as needed).

This approach is great if you later need to adjust counts based on report groups (e.g., totals per department or date range) — you can just reconfigure the summary without touching your SQL.

内容的提问来源于stack exchange，提问作者Shahad g