Sql-server – Group By With Rollup results table has totals at the top

casegroup bysql server

I have the following query that returns a set of counts by weeks beginning with Monday, with a row for the aggregate totals. 

select distinct 
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) as B2B_Week,
    count (distinct bm.MISSION_ID) as B2B_Mission,
    count (distinct dl.DRIVER) as Distinct_B2B_Drivers
from 
    DD.B2B_MISSIONS bm
    inner join dd.IN_GATE ig on bm.MISSION_ID = ig.IN_MISSION_ID
    inner join dd.XNS_DRIVER_LOGIN dl on ig.IN_DRIVER_ID = dl.driver 
group by 
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) 
  with rollup
order by 
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM)
;

My Results Table looks like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
            1647        357
44          717         264
45          930         301

I want it to look like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
44          717         264
45          930         301
TOTAL       1647        357

ISSUES

  1. The totals row appears at the top rather than the bottom. This is
    probably related to the second problem.

  2. I have been unable to use a CASE statement to return 'TOTAL' with
    the date column is NULL. The primary problem is that the DATE is an
    INT and 'TOTAL' is not. I have tried various methods of using CAST
    on either the entire CASE statement or just pieces of it. I've tried putting the Case statement in the SELECT or in the GROUP BY statement.

    Here is an example of what happens when I try to get TOTAL to appear.

    case when DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) is null 
then 'TOTAL' else DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) end

    "Conversion failed when converting the varchar value 'TOTAL' to data type int."

What am I doing wrong?

Best Answer

You should use the GROUPING function

GROUPING returns 1 for aggregated or 0 for not aggregated in the result set.

The advantage of this is that it will deal correctly with the case that B2B_Week is itself NULL and not incorrectly treat it as a total row.

WITH T
     AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
                bm.MISSION_ID,
                dl.DRIVER
         FROM   DD.B2B_MISSIONS bm
                INNER JOIN dd.IN_GATE ig
                  ON bm.MISSION_ID = ig.IN_MISSION_ID
                INNER JOIN dd.XNS_DRIVER_LOGIN dl
                  ON ig.IN_DRIVER_ID = dl.driver)
SELECT B2B_Week = CASE WHEN GROUPING(B2B_Week) = 0 THEN CAST(B2B_Week AS VARCHAR(5)) ELSE 'Total' END,
       B2B_Mission = COUNT (DISTINCT MISSION_ID),
       Distinct_B2B_Drivers = COUNT (DISTINCT DRIVER)
FROM   T
GROUP  BY B2B_Week WITH ROLLUP
ORDER  BY GROUPING(B2B_Week),
          T.B2B_Week
Related Question