Write an SQL query that reports the total quantity sold for every product id.
Input Table : sales
sale_id | product_id | sale_year | quantity | price |
20 | 80 | 2017 | 21 | 300 |
30 | 80 | 2018 | 23 | 400 |
40 | 13 | 2019 | 56 | 600 |
Output Table:
product_id | total_quantity |
80 | 44 |
13 | 56 |
Explanation:
- product_id is Foreign Key and (sale_id, year) is primary key of “sales” table.
Fetch total quantity of units sold for each product collectively in all years.
DDLs:
CREATE TABLE sales ( sale_id INT, product_id INT, sale_year INT, quantity INT, price INT ); INSERT INTO sales VALUES (20, 80, 2017, 21, 300); INSERT INTO sales VALUES (30, 80, 2018, 23, 400); INSERT INTO sales VALUES (40, 13, 2019, 56, 600);
Solution:
SELECT product_id, SUM(quantity) total_quantity FROM sales GROUP BY product_id ;