6. 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

1706519353859257 Image scaled to 90%

Output

1706519398453326 Image scaled to 50%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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             |
+--------------+----------------+