﻿ AdventureWorks: Total quantity of each product in specific shelves

# 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.

Sample table: production.productinventory

Click to view Full table

Sample Solution:

SELECT productid, sum(quantity) AS total_quantity
FROM production.productinventory
WHERE shelf IN ('A','C','H')
GROUP BY productid
HAVING SUM(quantity)>500
ORDER BY productid;

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|
...

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿