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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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