AdventureWorks Database: Empty group as one of the elements of a GROUPING SET

SQL Query - AdventureWorks: Exercise-15 with Solution

15. From the following table write a query in SQL to find the total quantity for each locationid and calculate the grand-total for all locations. Return locationid and total quantity. Group the results on locationid.

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 the location ID and the sum of quantity
SELECT locationid, SUM(quantity) AS TotalQuantity
-- Retrieving data from the 'productinventory' table
FROM production.productinventory
-- Grouping the results using grouping sets
GROUP BY GROUPING SETS ( locationid, () );

Explanation:

• The SELECT statement retrieves the location ID 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 grouping sets.
• GROUPING SETS ( locationid, () ) defines the grouping sets used for aggregation.
• locationid groups the results by the location ID, providing subtotals for each location.
• () represents an empty set, indicating that a grand total is also calculated across all rows.
• This query generates subtotals for each location ID and an overall grand total across all locations.

Sample Output:

locationid|totalquantity|
----------+-------------+
          |       335974|
         4|          110|
        30|          958|
        50|        95477|
        40|          508|
        60|        20419|
         3|          186|
        20|         5165|
         7|        17319|
        10|        13584|
         1|        72899|
        45|          332|
         5|        20295|
         2|         5549|
         6|        83173|
		...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Combining multiple GROUP BY clauses into one.
Next: Count employees for each city group by using multiple tables.