CODEWITHSUNDEEP
sqlsql-serversql-server-2017
willuwontu
willuwontu

Reputation: 159

ROLLUP on multiple columns

I'm counting the number of of entities that have been received between 2 dates or have been resolved between those same 2 dates, and then grouping them by their source.

Currently my code is

SELECT 
    CASE WHEN GROUPING(EntitySource) = 1 THEN 'Total' ELSE EntitySource END EntitySource, 
    (
        SELECT COUNT(PostmarkDate) 
        FROM tblEntity t1
        Where ((PostmarkDate BETWEEN @StartDate AND @EndDate)) AND t1.EntitySource = t.EntitySource
    ) AS Recieved, 
    COUNT(ResolDate) 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

Using this query results in a table of

EntitySource    Recieved    Completed
D               79          175
B               272         614
C               19          13
E               1           6
A               1           0
F               3           12
Total           0           820

As you can see ROLLUP is correctly totaling up the completed entities column, but it's not adding up the received column at all. The total for that column should be displaying 375, giving a total row of

Total           375         820

Upvotes: 0

Views: 3952

Answers (1)

LukStorms
LukStorms

Reputation: 29667

The rollup works on the aggregate functions.
So counting or summing a CASE also does the trick. 

SELECT
   COALESCE(EntitySource,'Total') AS EntitySource,
   COUNT(CASE WHEN PostmarkDate BETWEEN @StartDate AND @EndDate THEN 1 END) AS Received, 
   COUNT(ResolDate) 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
 GROUPING(EntitySource),
 (CASE EntitySource
  WHEN 'D' THEN 1
  WHEN 'B' THEN 2
  WHEN 'C' THEN 3
  WHEN 'E' THEN 4
  WHEN 'A' THEN 5
  WHEN 'F' THEN 6
  ELSE 9
  END)

Upvotes: 1

Related Questions