AdventureWorks Database: Combining multiple GROUP BY clauses into one

SQL Query - AdventureWorks: Exercise-14 with Solution

14. From the following table write a query in SQL to find the sum of the quantity with subtotal for each locationid. Group the results for all combination of distinct locationid and shelf column. Rolls up the results into subtotal and running total. Return locationid, shelf and sum of quantity as TotalQuantity.

productid|locationid|shelf|bin|quantity|rowguid                             |modifieddate           |
---------+----------+-----+---+--------+------------------------------------+-----------------------+
        1|         1|A    |  1|     408|47a24246-6c43-48eb-968f-025738a8a410|2014-08-08 00:00:00.000|
        1|         6|B    |  5|     324|d4544d7d-caf5-46b3-ab22-5718dcc26b5e|2014-08-08 00:00:00.000|
        1|        50|A    |  5|     353|bff7dc60-96a8-43ca-81a7-d6d2ed3000a8|2014-08-08 00:00:00.000|
        2|         1|A    |  2|     427|f407c07a-ca14-4684-a02c-608bd00c2233|2014-08-08 00:00:00.000|
        2|         6|B    |  1|     318|ca1ff2f4-48fb-4960-8d92-3940b633e4c1|2014-08-08 00:00:00.000|
        2|        50|A    |  6|     364|d38cfbee-6347-47b1-b033-0e278cca03e2|2014-08-08 00:00:00.000|
        3|         1|A    |  7|     585|e18a519b-fb5e-4051-874c-58cd58436c95|2008-03-31 00:00:00.000|
        3|         6|B    |  9|     443|3c860c96-15ff-4df4-91d7-b237ff64480f|2008-03-31 00:00:00.000|
        3|        50|A    | 10|     324|1339e5e3-1f8e-4b82-a447-a8666a264f0c|2008-03-31 00:00:00.000|
        4|         1|A    |  6|     512|6beaf0a0-971a-4ce1-96fe-692807d5dc00|2014-08-08 00:00:00.000|
        4|         6|B    | 10|     422|2c82427a-63f1-4877-a1f6-a27b4d201eb6|2014-08-08 00:00:00.000|
        4|        50|A    | 11|     388|fd912e69-efa2-4ab7-82a4-03f5101af11c|2014-08-08 00:00:00.000|
      316|         5|A    | 11|     532|1ee3dbd3-2a7e-47dc-af99-1b585575efb9|2008-03-31 00:00:00.000|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting columns for location ID, shelf, and the sum of quantity
SELECT locationid, shelf, SUM(quantity) AS TotalQuantity
-- Retrieving data from the 'productinventory' table
FROM production.productinventory
-- Grouping the results using both ROLLUP and CUBE functions
GROUP BY GROUPING SETS ( ROLLUP (locationid, shelf), CUBE (locationid, shelf) );

Explanation:

• The SELECT statement retrieves columns for location ID, shelf, and the sum of quantity.
• FROM production.productinventory specifies the table 'productinventory' from which the data will be retrieved.
• The GROUP BY clause groups the results using both ROLLUP and CUBE functions.
• ROLLUP (locationid, shelf) generates subtotals for the combinations of location ID and shelf, as well as grand totals for each individual column and the overall total.
• CUBE (locationid, shelf) produces a result set that includes super-aggregate rows, representing all combinations of specified columns along with grand totals and subtotals.
• GROUP BY GROUPING SETS allows specifying multiple grouping sets in the same query, combining the results of ROLLUP and CUBE functions.

Sample Output:

locationid|shelf|totalquantity|
----------+-----+-------------+
          |     |       335974|
          |     |       335974|
        50|J    |         3321|
         6|A    |         2734|
        50|B    |         3591|
         5|A    |         6572|
        45|N/A  |          332|
        60|K    |          107|
        10|D    |         1727|
        60|G    |         1050|
        60|A    |         1116|
        20|B    |          355|
        50|S    |         1333|
        50|K    |         8881|
         1|G    |         3954|
         6|K    |          164|
        20|A    |         1680|
		...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: GROUP BY CUBE operation on locationid and shelf.
Next: Empty group as one of the elements of a GROUPING SET.