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

Last update on December 20 2024 11:57:03 (UTC/GMT +8 hours)

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.

Sample table: production.productinventory

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.