Mysql Question posted in
The official MySQL documentation can be found here.

Retrieve total sales for each product category in MySQL

I have two tables in my MySQL database: products and sales. The products table has the following structure:
Table: products

------------------------
| id | name    | category |
------------------------
| 1  | Product A | Electronics |
| 2  | Product B | Clothing    |
| 3  | Product C | Electronics |
| 4  | Product D | Furniture   |
| 5  | Product E | Clothing    |
| ...| ...      | ...         |
------------------------

The sales table records the sales transactions with the following structure:
Table: sales

------------------------
| id | product_id | quantity |
------------------------
| 1  | 1          | 5        |
| 2  | 2          | 10       |
| 3  | 1          | 3        |
| 4  | 4          | 8        |
| 5  | 2          | 2        |
| ...| ...        | ...      |
------------------------

I want to retrieve the total sales quantity for each product category. The result should display the category name and the sum of quantities sold for products in that category.

Desired Result:

------------------------
| category     | total_sales |
------------------------
| Electronics  | 8           |
| Clothing     | 12          |
| Furniture    | 8           |
------------------------

Could you please provide me with the SQL query to achieve this result? I’m new to SQL and would greatly appreciate your help. Thank you!

Tags:

2

Answers


  1. 0

    You need a join between the two tables followed by an aggregation:

    SELECT p.category, COALESECE(SUM(s.quantity), 0) AS total_sales
FROM products p
LEFT JOIN sales s
    ON s.product_id = p.id
GROUP BY p.category;
    Login or Signup to reply.
  2. 0

    Try this :

    SELECT a.id, a.name, COALESCE(d.Totalsales, 0) AS Totalsales
FROM Products  a
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS Totalsales
    FROM Sales
    GROUP BY product_id
) d ON a.id = d.product_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search