SQL 301 – GROUPING

In our previous lesson we covered WITH ROLLUP  and WITH CUBE.  During that lesson you may have noticed that when we saw the summary rows, the columns had NULL as the value.  Hopefully you wondered to yourself, how do I know programatically which of the rows are summary lines and which are rows that just had NULL for the value of that column?

Well that’s where GROUPING() comes in.  With GROUPING(), you get a 1 where the row is a summary row, and 0 if it isn’t.

Let’s look at our previous WITH CUBE query.

SELECT Type, Store, SUM(Number) as Number
FROM items
GROUP BY type,store
WITH CUBE

We got the results:

Type Store Number
Shield Tatooine 18
Shield Korriban 9
Shield NULL 27
Blaster Tatooine 12
Blaster Korriban 5
Blaster Dantooine 14
Blaster NULL 31
Lightsaber Crystal Korriban 1
Lightsaber Crystal Dantooine 4
Lightsaber Crystal NULL 5
NULL NULL 63
NULL Tatooine 30
NULL Korriban 15
NULL Dantooine 18

In this case all of those NULLs are summary lines, so if we changed the query to:

SELECT
    CASE WHEN Type IS NOT NULL THEN Type ELSE
      CASE WHEN GROUPING(Type) = 1 THEN
        CASE WHEN GROUPING(store) = 0 'Subtotal Type' ELSE 'Grand Total' END
      ELSE
        'N/A'
      END
    END as Type
  , CASE WHEN Store IS NOT NULL THEN Type ELSE
      CASE WHEN GROUPING(Store) = 1 THEN
        CASE WHEN GROUPING(Type) = 0 'Subtotal Store' ELSE 'Grand Total' END
      ELSE
        'N/A'
      END
    END as Store
  , SUM(Number) as Number
FROM items
GROUP BY type,store
WITH CUBE

We’d get a bit nicer output.

Type Store Number
Shield Tatooine 18
Shield Korriban 9
Shield Subtotal Store 27
Blaster Tatooine 12
Blaster Korriban 5
Blaster Dantooine 14
Blaster Subtotal Store 31
Lightsaber Crystal Korriban 1
Lightsaber Crystal Dantooine 4
Lightsaber Crystal Subtotal Store 5
Grand Total Grand Total 63
Subtotal Type Tatooine 30
Subtotal Type Korriban 15
Subtotal Type Dantooine 18

Or, at the very least we’d have a result that tells us exactly what we’re looking at on each row.

Have any questions?  Let me know!  I’m here to help.

Leave a Reply

Your email address will not be published. Required fields are marked *