w3resource

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

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Ref : https://bit.ly/3VKT5M8