AdventureWorks Database: Find total quantity of each product in specific shelves

SQL Query - AdventureWorks: Exercise-9 with Solution

9. From the following table write a query in SQL to retrieve total quantity of each productid which are in shelf of 'A' or 'C' or 'H'. Filter the results for sum quantity is more than 500. Return productid and sum of the quantity. Sort the results according to the productid in ascending order.

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 product ID and the total quantity of products from the 'productinventory' table
SELECT productid, sum(quantity) AS total_quantity
-- Filtering the data to include only products located on shelves A, C, or H
FROM production.productinventory
WHERE shelf IN ('A','C','H')
-- Grouping the results by product ID
GROUP BY productid
-- Filtering the grouped results to include only those with a total quantity greater than 500
HAVING SUM(quantity) > 500
-- Ordering the result set by product ID
ORDER BY productid;

Explanation:

• The SELECT productid, sum(quantity) AS total_quantity statement retrieves the product ID and calculates the total quantity of products for each product ID.
• FROM production.productinventory specifies the table from which the data will be retrieved. 'production' is the schema name and 'productinventory' is the table name.
• The WHERE shelf IN ('A','C','H') clause filters the data to include only products located on shelves A, C, or H.
• The GROUP BY productid clause groups the results by product ID, allowing the calculation of the sum of quantities for each product.
• The HAVING SUM(quantity) > 500 clause filters the grouped results to include only those with a total quantity greater than 500.
• The ORDER BY productid clause orders the result set by product ID.

Sample Output:

productid|total_quantity|
---------+--------------+
        1|           761|
        2|           791|
        3|           909|
        4|           900|
      316|           532|
      317|           593|
      319|           797|
      320|          1136|
      321|          1750|
      322|          1684|
      323|          1684|
      324|          1629|
      325|          1210|
      326|          1097|
      328|          1044|
      329|          1025|
      330|          1005|
      331|           831|
	  ...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the average and sum of the subtotal.
Next: Expression used in group by.