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.

Sample table: production.productinventory

Click to view Full table

Sample Solution:

SELECT locationid, SUM(quantity) AS TotalQuantity
FROM production.productinventory
GROUP BY GROUPING SETS ( locationid, () );

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.