Calculate sales throughout categories

Question

Hello, everyone.

The examples below is obviously an extreme pseudo-example, but it represents the problem I'm facing very well.

I have been struggeling with this for some time now and I have yet to find a great solution.

I need to print out a list of all the categories (Parent categories too! With their sub-categories total amounts of sales summed up) with their total sales from the products - Ordered by the total amount of sales.

What I need printed

+-------------+---------------+-------+
| category_id |   category    | sales |
+-------------+---------------+-------+
|           1 | shirts        |  1100 |
|           2 | t-shirts      |  1000 |
|           3 | tank-tops     |   100 |
|           4 | hats          |   100 |
|           5 | baseball caps |    50 |
|           6 | beanies       |    50 |
+-------------+---------------+-------+

Tables

Table: categories
+-------------+--------------+
| category_id |   category   |
+-------------+--------------+
|           1 | shirts       |
|           2 | t-shirts     |
|           3 | tank-tops    |
|           4 | hats         |
|           5 | baseball caps|
|           6 | beanie       |
+-------------+--------------+

Table: categories_to_categories
+-----------------------------+---------------+----------------------+
| category_to_category_id | category_id | parent_category_id |
+-----------------------------+---------------+----------------------+
|                           1 |             1 |                    0 |
|                           2 |             2 |                    1 |
|                           3 |             3 |                    1 |
|                           4 |             4 |                    0 |
|                           5 |             5 |                    4 |
|                           6 |             6 |                    4 |
+-----------------------------+---------------+----------------------+

Table: products
+------------+-------------+------------------+-------+
| product_id | category_id |       name       | sales |
+------------+-------------+------------------+-------+
|          1 |           2 | black t-shirt    |   600 |
|          2 |           2 | blue t-shirt     |   400 |
|          3 |           3 | white tank-top   |   100 |
|          4 |           5 | red baseball cap |    50 |
|          5 |           6 | yellow beanie    |    50 |
+------------+-------------+------------------+-------+

Is this at all possible? I've tried with some recursive funtions throughout PHP, but it's insanely slow and certainly not the most optimal way.

Answer 1

In your table schema, you need to write a subquery use UNION ALL to combine two results set from categories_to_categories

1. Price By parent_category_id
2. Price By category_id

then do OUTER JOIN base on categories.

CREATE TABLE categories( category_id INT, category VARCHAR(50));

INSERT INTO categories VALUES (1 ,'shirts'); 
INSERT INTO categories VALUES (2 ,'t-shirts'); 
INSERT INTO categories VALUES (3 ,'tank-tops'); 
INSERT INTO categories VALUES (4 ,'hats'); 
INSERT INTO categories VALUES (5 ,'baseball caps'); 
INSERT INTO categories VALUES (6 ,'beanie'); 

CREATE TABLE categories_to_categories( 
  category_to_category_id INT, 
  category_id INT,
  parent_category_id INT
);

INSERT INTO categories_to_categories VALUES ( 1 ,1 , 0 );
INSERT INTO categories_to_categories VALUES ( 2 ,2 , 1 );
INSERT INTO categories_to_categories VALUES ( 3 ,3 , 1 );
INSERT INTO categories_to_categories VALUES ( 4 ,4 , 0 );
INSERT INTO categories_to_categories VALUES ( 5 ,5 , 4 );
INSERT INTO categories_to_categories VALUES ( 6 ,6 , 4 );


CREATE TABLE products( 
  product_id INT, 
  category_id INT,
  name varchar(50),
  sales int
);

INSERT INTO products VALUES ( 1 ,2 , 'black t-shirt', 600 );
INSERT INTO products VALUES ( 2 ,2 , 'blue t-shirt', 400 );
INSERT INTO products VALUES ( 3 ,3 , 'white tank-top', 100 );
INSERT INTO products VALUES ( 4 ,5 , 'red baseball cap',  50 );
INSERT INTO products VALUES ( 5 ,6 , 'yellow beanie',  50 );

Query 1:

select   C.category_id, C.category,sum(sales) sales
from categories c
LEFT JOIN (
  SELECT parent_category_id as 'category_id',sales 
  FROM categories_to_categories c1
  INNER JOIN products p ON p.category_id  = c1.category_id 
  UNION ALL
  SELECT c1.category_id,sales 
  FROM categories_to_categories c1
  INNER JOIN products p ON p.category_id  = c1.category_id 
) ctc on c.category_id = ctc.category_id
group by C.category_id, C.category

Results:

| category_id |      category | sales |
|-------------|---------------|-------|
|           1 |        shirts |  1100 |
|           2 |      t-shirts |  1000 |
|           3 |     tank-tops |   100 |
|           4 |          hats |   100 |
|           5 | baseball caps |    50 |
|           6 |        beanie |    50 |

Answer 2

use aggregate function and join

   select c.category_id, 
   c.category ,sum(p.sales) as sum_sales from 
   product p inner join
   categories_to_categories ctc 
   on p.category_id=ctc.category_id
   inner join 
   categories c on ctc.parent_category_id=c.category_id     
   group by c.category_id, c.category
   order by sum_sales desc