SQL Server, multiple "GROUP BY" in a single statement? The GROUPING SETS explained in a CLEAR way and with EXAMPLES!

Hi Guys! 

Welcome back!
Today we will talk about GROUPING SETS.

As usual I will try to explain what we are talking about in the clearest possible way and we will always give examples.

So what is a GROUPING SET?

GROUPING SETS can be considered an extension of the GROUP BY clause in fact it gives you the possibility to combine multiple GROUP BY clauses.

How many times do we have to show in video a series of data that maybe adding them up and regrouping them for more than one field?

How many times have we had to use UNION clauses?

Often many clauses union together make the code less readable and less maintainable.

The solution is to use GROUPING SETS.

Enjoy the reading!

 

GROUPING SETS 

As said, a grouping set can be considered as an extension of the GROUP BY clause.

Speaking in a technical way, SQL grouping sets are a way to group data in SQL queries by multiple dimensions.

Now, in a typical SQL GROUP BY clause, data is grouped by one or more columns.

For example, if you have a table of sales data with columns for year, month, and sales, you might use a GROUP BY clause to group the data by year and month, like this:

SELECT YEAR, MONTH, SUM(SALES) FROM ORDTES GROUP BY YEAR, MONTH
   

This would give you the total sales for each combination of year and month.

With grouping sets, you can group the data by multiple combinations of columns at once.

For example, you might want to group the data by year and month, as well as by year alone and by product alone (or for any other group we need).

 

EXAMPLE

 

For the example we will use the our usual table ORDTES that is defined as follow:

CREATE TABLE ORDTES (ID INT identity(1,1) , YEARDOC INT, MONTHDOC INT, NUMDOC INT, TOTDOC FLOAT)&nbsp

 

In this table we have only the fields:

• yeardoc representing the year of the document
• monthdoc representing the month of the document
• numdoc representing the document number
• totdoc representing the total amount of the document

We insert in the table some data:

       
INSERT INTO ORDTES (YEARDOC, MONTHDOC, NUMDOC, TOTDOC)
VALUES (2022,1,1,10),(2022,1,2,20),(2022,2,3,10),(2022,2,4,20),(2023,1,1,50),(2023,1,2,25),(2023,2,3,10),(2023,2,4,30) 
 

 

So, these are our data:

 

Now, suppose you have to show the total sum, for each year, of the document amounts for each month

then suppose you have to show the total sum of document amounts for each year

Usually we can write two questions and “merge” them through a UNION.

As in the example below:

(SELECT
    YEARDOC,
    MONTHDOC,
    SUM(TOTDOC)
FROM
    ORDTES
GROUP BY YEARDOC, MONTHDOC)

UNION

(SELECT
    YEARDOC,
    NULL AS MONTHDOC,
    SUM(TOTDOC)
FROM
    ORDTES
GROUP BY YEARDOC)

In case, however, that we have many UNIONs our query will become long , less readable and little maintainable.

And so it is much clearer to write it using GROUPING SETS.
This below is the syntax:

      
SELECT
    YEARDOC,
    MONTHDOC,
    SUM(TOTDOC)
FROM
    ORDTES
GROUP BY
    GROUPING SETS (
        (YEARDOC, MONTHDOC),
        (YEARDOC)
     )

we simply use the syntax:

      
GROUP BY
    GROUPING SETS ( group1, group2, etc etc ... )
        

between the brackets we can indicate more groups:

For Example:

• Group 1 is (yeardoc, monthdoc)
• Group 2 is (yeardoc) 

And voilà this is the result:

This solution is easier, more readable and more maintainable!
Especially if the select to join with the union are many

That’s all for today.

~Luke

Previous post: "SQL Query Design Patterns and Best Practices" My review!