Product Sales Analysis II

Problem

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Problem Definition

Write a solution that reports the total quantity sold for every product id.

Example

Output

Try It Yourself

Solution

We can simply select the product_id and calculate the sum of the quantity column as total_quantity from the Sales table. Then, we group the results by product_id.

SELECT product_id,
       Sum(quantity) AS total_quantity
FROM   Sales
GROUP  BY product_id 

Let's break down the query step by step:

Step 1: Select fields

SELECT product_id,
       Sum(quantity) AS total_quantity
FROM   Sales

The SELECT clause specifies the columns that will be included in the result set. In this case, it selects the product_id and the sum of the quantity for each product.

Output After Step 1:

+------------+----------------+
| product_id | total_quantity |
+------------+----------------+
| 100        | 22             |
| 200        | 15             |
+------------+----------------+

Step 2: GROUP BY product_id:

GROUP  BY product_id 

The GROUP BY clause is used to group the results by a specific column or columns. In this query, it groups the sales data by the product_id.

The SUM(quantity) function will then be applied to each group separately, calculating the total quantity for each distinct product_id.

Final Output:

+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+