Description
Write a SQL query to find total sales amount for each product. Return product name and total sales, ordered by total descending. **Output columns (in order):** name, total_sales
Table:
products| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
Table:
sales| Column | Type |
|---|---|
| id | INT |
| product_id | INT |
| amount | INT |
Examples
Input:
CREATE TABLE products (id INT, name TEXT); CREATE TABLE sales (id INT, product_id INT, amount INT); INSERT INTO products VALUES (1, 'Widget'), (2, 'Gadget'); INSERT INTO sales VALUES (1, 1, 1000), (2, 1, 500), (3, 2, 1000);Output:
Widget|1500
Gadget|1000Explanation:
Widget has two sales (1000 + 500 = 1500), Gadget has one sale (1000). Results are ordered by total sales descending.
Input:
CREATE TABLE products (id INT, name TEXT); CREATE TABLE sales (id INT, product_id INT, amount INT); INSERT INTO products VALUES (1, 'Laptop'), (2, 'Mouse'), (3, 'Keyboard'); INSERT INTO sales VALUES (1, 1, 1200), (2, 2, 25), (3, 1, 800), (4, 3, 150), (5, 2, 30), (6, 3, 100);Output:
Laptop|2000
Keyboard|250
Mouse|55Explanation:
Laptop has two sales (1200 + 800 = 2000), Keyboard has two sales (150 + 100 = 250), and Mouse has two sales (25 + 30 = 55). Results are ordered by total sales descending.
Input:
CREATE TABLE products (id INT, name TEXT); CREATE TABLE sales (id INT, product_id INT, amount INT); INSERT INTO products VALUES (1, 'Phone'), (2, 'Tablet'), (3, 'Headphones'); INSERT INTO sales VALUES (1, 2, 500), (2, 1, 750), (3, 2, 300);Output:
Tablet|800
Phone|750Explanation:
Only Phone and Tablet have sales records. Tablet has total sales of 800 (500 + 300), Phone has 750. Headphones appears in products but has no sales, so it is excluded from results by the JOIN and SUM operations.
Constraints
- •
Use JOIN and SUM