Retrieve total sales for each product category in MySQL

ghz 9months ago ⋅ 67 views

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!

Answers

To retrieve the total sales quantity for each product category, you can use a SQL query that joins the products and sales tables and then groups the results by the category column. You can then calculate the sum of the quantity column for each group. Here's the SQL query to achieve the desired result:

SELECT
    p.category AS category,
    SUM(s.quantity) AS total_sales
FROM
    products p
JOIN
    sales s ON p.id = s.product_id
GROUP BY
    p.category;

Explanation:

  1. We use the SELECT statement to specify the columns we want to retrieve in the result set. In this case, we want to retrieve the category column from the products table and the sum of the quantity column from the sales table.

  2. We use the FROM clause to specify the tables from which we are retrieving data. We specify products p to refer to the products table and sales s to refer to the sales table.

  3. We use the JOIN keyword to join the products and sales tables based on the id column from the products table and the product_id column from the sales table.

  4. We use the GROUP BY clause to group the results by the category column from the products table. This groups the sales data by product category.

  5. Finally, we use the SUM() function to calculate the sum of the quantity column for each group. This gives us the total sales quantity for each product category.

This SQL query will return the desired result showing the total sales quantity for each product category.

#mysql