Product Sales Analysis II - Problem

Given two tables Sales and Product, write a SQL solution to report the total quantity sold for every product id.

The Sales table contains sales records with columns:

  • sale_id (int): Sale identifier
  • product_id (int): Product identifier (foreign key)
  • year (int): Sale year
  • quantity (int): Units sold
  • price (int): Price per unit

The Product table contains product information with columns:

  • product_id (int): Product identifier (primary key)
  • product_name (varchar): Product name

Return the resulting table in any order.

Table Schema

Sales
Column Name Type Description
sale_id PK int Sale identifier
product_id int Product identifier (foreign key to Product table)
year PK int Year of the sale
quantity int Number of units sold
price int Price per unit
Primary Key: (sale_id, year)
Product
Column Name Type Description
product_id PK int Product identifier (primary key)
product_name varchar Name of the product
Primary Key: product_id

Input & Output

Example 1 — Multiple Sales Per Product
Input Tables:
Sales
sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2009 12 5000
7 200 2011 15 9000
Product
product_id product_name
100 Nokia
200 Apple
300 Samsung
Output:
product_id total_quantity
100 22
200 15
💡 Note:

Product 100 (Nokia) has two sales records: 10 units in 2008 and 12 units in 2009, totaling 22 units. Product 200 (Apple) has one sale record with 15 units. Product 300 (Samsung) has no sales, so it doesn't appear in the result.
Example 2 — Single Sale Per Product
Input Tables:
Sales
sale_id product_id year quantity price
1 100 2008 10 5000
2 200 2009 25 3000
3 300 2010 35 8000
Product
product_id product_name
100 Nokia
200 Apple
300 Samsung
Output:
product_id total_quantity
100 10
200 25
300 35
💡 Note:

Each product has exactly one sale record, so the total quantity equals the individual sale quantity. Product 100 sold 10 units, product 200 sold 25 units, and product 300 sold 35 units.
Example 3 — Empty Sales Table
Input Tables:
Sales
sale_id product_id year quantity price
Product
product_id product_name
100 Nokia
200 Apple
Output:
product_id total_quantity
💡 Note:

When there are no sales records, the result is empty since GROUP BY only creates groups for existing data. Products with no sales don't appear in the output.

Constraints

  • 1 ≤ sale_id ≤ 1000
  • 1 ≤ product_id ≤ 1000
  • 2000 ≤ year ≤ 2020
  • 1 ≤ quantity ≤ 100
  • 1 ≤ price ≤ 10000

Visualization

Tap to expand
Product Sales Analysis II OverviewSales Table (Input)product_idquantity100101001220015GROUP BYSUM()Result (Output)product_idtotal_quantity1002220015Multiple rows per product → Single row with totalKey Operation:GROUP BY product_idSUM(quantity) AS total_quantity
Understanding the Visualization
1
Input
Sales table with multiple records per product
2
GROUP BY
Group sales records by product_id
3
SUM
Calculate total quantity for each product
Key Takeaway
🎯 Key Insight: Use GROUP BY with aggregate functions to consolidate multiple rows into summary statistics

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
34.5K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen