Using an aggregate for totals using Rollup

Question

I am experimenting with using Rollup, and wanted to find out if there's a way to achieve this without using a subquery, CTE or Temp Table. Here's my code:

    select coalesce(Answer,case Grouping_ID(Answer) when 1 then 'Total' end) as Answer
    , Count(*) as CSAT_Count
    , Case Answer
        when 'Average' then 2*count(*)
        when 'Outstanding' then 3*Count(*)
        when 'Unsatisfactory' then 1*Count(*) 
      end as CSAT_Score
from CSAT_Table
Where Empl_ID = 98
Group by Answer
with rollup

Here's the result:

     Answer               CSAT_Count    CSAT_Score
    ----------------------------------------------
     Average              13            26
     Outstanding          126           378
     Unstatisfactory      6             6
     Total                145           NULL

I'd like to have the NULL returning in CSAT_Score reflect the sum of the scores, but I'm thinking because the score is relying on COUNT(*) to be calculated, I can't get Rollup to do this for me, since I can't group by an aggregate.

Like I said above, I know a subquery, CTE or Temp table storing the result of the count would work, but since I'm new to using Rollup, I wanted to see if there was something else I could do with that function to make this work.

Thanks

Just curious: Why are you so heavily against a solution with a CTE or Temp Table? — SQL_M, Mar 22 '18 at 16:06
@SQL_M *since I'm new to using Rollup, I wanted to see if there was something else I could do with that function to make this work.* — iamdave, Mar 22 '18 at 16:08
As far as I am aware, you cannot do this using `with rollup`, so the derived table route is your best bet. — iamdave, Mar 22 '18 at 16:09
@SQL_M - I'm not against it, just trying to make sure I understand how Rollup works. I know exactly how I'd do this with a CTE/Temp Table. — Corrie, Mar 22 '18 at 17:37

Serkan Arslan · Accepted Answer · 2018-03-22T16:21:45.307

0

This version of your script working with rollup. ( by the way, it produces the same result )

  select coalesce(Answer,case Grouping_ID(Answer) when 1 then 'Total' end) as Answer
    , Count(*) as CSAT_Count
    , Sum(Case Answer
        when 'Average' then 2
        when 'Outstanding' then 3
        when 'Unsatisfactory' then 1
      end) as CSAT_Score
from CSAT_Table
Where Empl_ID = 98
Group by Answer
with rollup

edited Mar 22 '18 at 16:21

answered Mar 22 '18 at 16:11

Serkan Arslan

13,158
4
29
44

That does it!! Thanks!! – Corrie Mar 22 '18 at 17:41

Using an aggregate for totals using Rollup

1 Answers1