ROLLUP多列统计异常：Received列总计值未正确计算

Let's break down why your Recieved column's total is showing 0, and how to fix it quickly:

The Root Cause #

Your original query uses a correlated subquery to calculate Recieved:

( SELECT COUNT(PostmarkDate) FROM tblEntity t1 Where ((PostmarkDate BETWEEN @StartDate AND @EndDate)) AND t1.EntitySource = t.EntitySource ) AS Recieved

When the ROLLUP generates the Total row, t.EntitySource becomes NULL (since GROUPING(EntitySource) = 1). The subquery tries to match t1.EntitySource = NULL, which never returns any rows—hence the 0 value.

The Fix: Use Conditional Aggregation Instead #

Instead of a correlated subquery, calculate both Recieved and Completed directly with conditional aggregation. This works seamlessly with ROLLUP because it doesn't depend on grouping-specific values in the outer query.

Here's the revised query:

SELECT 
    CASE WHEN GROUPING(EntitySource) = 1 THEN 'Total' ELSE EntitySource END EntitySource,
    -- Count all records with PostmarkDate in the range (regardless of completion)
    COUNT(CASE WHEN PostmarkDate BETWEEN @StartDate AND @EndDate THEN 1 END) AS Recieved,
    -- Count only completed records with ResolDate in the range
    COUNT(CASE WHEN IsCompleted = '1' AND ResolDate BETWEEN @StartDate AND @EndDate THEN 1 END) AS Completed
FROM tblEntity t
WHERE 
    (IsCompleted = '1' AND ResolDate BETWEEN @StartDate AND @EndDate) 
    OR (PostmarkDate BETWEEN @StartDate AND @EndDate)
GROUP BY EntitySource WITH ROLLUP
ORDER BY 
    CASE WHEN EntitySource = 'D' THEN 1 ELSE 2 END,
    CASE WHEN EntitySource = 'B' THEN 1 ELSE 2 END,
    CASE WHEN EntitySource = 'C' THEN 1 ELSE 2 END,
    CASE WHEN EntitySource = 'E' THEN 1 ELSE 2 END,
    CASE WHEN EntitySource = 'A' THEN 1 ELSE 2 END,
    CASE WHEN EntitySource = 'F' THEN 1 ELSE 2 END

Why This Works #

• For each row in the group (including the Total row), the conditional COUNT checks if the date condition is met and counts valid entries.
• The Total row will sum up all matching records across all EntitySource values, giving you the correct 375 for Recieved while keeping the correct 820 for Completed.

Alternatively, you could use SUM instead of COUNT if you prefer—they'll produce the same result:

SUM(CASE WHEN PostmarkDate BETWEEN @StartDate AND @EndDate THEN 1 ELSE 0 END) AS Recieved

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