Make table values as column in MySQL

Question

I have the following table and table data on my database:

BRANCH_TABLE

BRANCH_CODE IS_ACTIVE
BRANCH1     1
BRANCH2     1
BRANCH3     1
BRANCH4     1
BRANCH5     1

PRODUCT_TABLE

PRODUCT_ID  PROD_NAME   PRODUCT_CATEGORY
1           PROD1       PROD_CATEGORY1
2           PROD2       PROD_CATEGORY1
3           PROD3       PROD_CATEGORY2
4           PROD4       PROD_CATEGORY2
5           PROD5       PROD_CATEGORY3

SALES_TABLE

SALES_ID    BRANCH_CODE     PRODUCT_ID  AMOUNT
1           BRANCH1         1           100.00
2           BRANCH2         1           100.00
3           BRANCH3         2           100.00
4           BRANCH4         2           100.00
5           BRANCH5         3           100.00
6           BRANCH1         3           100.00
7           BRANCH2         4           100.00
8           BRANCH3         4           100.00
9           BRANCH4         5           100.00
10          BRANCH5         5           100.00

Now I wanted to get the sales of each branch by product category. This is should be the expected output:

EXPECTED OUTPUT
BRANCH      PROD_CATEGORY1  PROD_CATEGORY2  PROD_CATEGORY3  TOTAL_SALES
BRANCH 1        100.00          100.00          0.00            200.00
BRANCH 2        100.00          100.00          0.00            200.00
BRANCH 3        100.00          100.00          0.00            200.00
BRANCH 4        100.00          0.00            100.00          200.00
BRANCH 5        0.00            100.00          100.00          200.00

Currently, what I'm doing is that I call the branches;

SELECT * FROM BRANCH_TABLE WHERE IS_ACTIVE = '1';

Then I call the product categories in the products table

SELECT DISTINCT(PRODUCT_CATEGORY) FROM PRODUCT_TABLE

Then I loop through the branches and product category result and get the sales per product category;

foreach($branches as $branch){
   foreach($product_categories as $category)
   {
      $sales = $sales_table->getSalesByCategory($branch, $category);
   }
}

// Sample Function on SalesTable Class
public function getSalesByCategory($branch, $category){
     $query = "
     SELECT
     SUM(A.AMOUNT) AS SALES
     FROM
     SALES_TABLE A, PRODUCT_TABLE B
     WHERE
     A.BRANCH_CODE = $branch
     AND
     B.PRODUCT_CATEGORY = $category
     AND
     A.PRODUCT_ID = B.PRODUCT_ID
     ";

     // Then bind the params and execute, I'll leave it here just to be short.
}

This algorithm works. However, as you can see I'm doing individual query just to get the amount of sales in each product category and each branch which is very slow. Specially when I got a 100k+ sales record and 100+ branches. That's why I want to come up with just a single query or at least fewer queries to achieve this table structure.

well, have you tried anything so far? if yes please show your attempt. — Barbaros Özhan
– Barbaros Özhan, Commented Aug 8, 2019 at 9:07
@BarbarosÖzhan I edited my post to explain further what I'm currently doing. — saionachi
– saionachi, Commented Aug 8, 2019 at 9:30

KDM · Accepted Answer · 2019-08-08 09:30:47Z

1

Not tested but will work

SELECT      ST.BRANCH_CODE
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY1' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY1
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY2' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY2
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY3' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY3
    ,   SUM(ST.AMOUNT) AS TOTAL
 FROM SALES_TABLE ST
 INNER JOIN PRODUCT_TABLE PT ON ST.PRODUCT_ID=PT.PRODUCT_ID
 GROUP BY ST.BRANCH_CODE
 ORDER BY ST.BRANCH_CODE

answered Aug 8, 2019 at 9:30

KDM

1448 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

saionachi Over a year ago

What if the product category is dynamic? Anyway I'll try this too. Maybe it will work for me because my product category is not dynamic.

KDM Over a year ago

If product_category is dynamic then you have to use pivot.

saionachi Over a year ago

Yeah it works on static product_category, Thanks buddy!

Collectives™ on Stack Overflow

Make table values as column in MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related