Group By With Rollup results table has totals at the top

Question

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?

Answer 1

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 

Answer 2

Using a nested query (or making the inner query a CTE) allows you to format and sort things where you otherwise cannot. This version inserts the word TOTAL and sorts correctly. NOTE: If you need to report across year ends, then additional sorting logic needs to be added.

SELECT 
    CASE
        WHEN B2B_Week IS NULL
            THEN 'TOTAL'
        ELSE Right('0' + CAST(B2B_Week AS varchar(5),2)
    END as B2B_Week,
    B2B_Mission,
    Distinct_B2B_Drivers
FROM (
    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
    ) as src
ORDER BY 
    CASE
        WHEN B2B_Week IS NULL
            THEN 'TOTAL'
        ELSE Right('0' + CAST(B2B_Week AS varchar(5),2)
    END