SQL Coding for Logical Operators question-CodePudding

Q: Using the retail. Inventory table, calculate what the total inventory would be for each product with 5 or less items in stock if all of the items on order were delivered immediately without any other sales taking place. The product_id for each product should be the first column in your results.

I need a sql code the one I am using is not working and I dont know another way to get the result my professor is asking for.

Expected:

product_id  ?column?
3   20
4   37
6   13
8   4
10  24
24  41
31  4
41  35
42  2
58  25
71  28

this is what I am using, but like I said it's wrong:

select product_id 
from retail.inventory
where total_on_hand <= 5;

CodePudding user response：

SELECT product_id, SUM(quantity_on_hand quantity_on_order) AS total_inventory_name FROM retail_inventory_tablename WHERE quantity_on_hand <= 5 GROUP BY product_id

can you try this.

CodePudding user response：

SELECT product_id, SUM(quantity_on_hand   quantity_on_order) AS total_inventory
FROM retail.inventory
WHERE quantity_on_hand   quantity_on_order <= 5
GROUP BY product_id

This query first selects the product_id and calculates the total inventory for each product by adding the quantity_on_hand and quantity_on_order columns. It then filters the results to only include products with 5 or less items in stock, and finally groups the results by product_id to calculate the total inventory for each product.