AdventureWorks Database: Empty group as one of the elements of a GROUPING SET
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.productinventoryproductid|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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics