Find all individual item purchase by all user in mysql

Question

I have three tables e.g client, product and purchase. Purchase:

 id     productId clientId     amount 
    1       1        2          2500
    2       2        3          3500
    3       3        4          4500
    4       6        1          5500
    5       1        2          1500
    6       3        3          2000
    7       3        2          1000

Client:

id    name
1     A
2     B
3     C
4     B

Product:

id    product
1      Apple
2      Banana
3      Mango
6      Sweet

I am able to query this

SELECT client.id, client.client_name, product.product, purchase.amount from client INNER JOIN purchase ON client.id=purchase.clientId INNER JOIN product ON product.id=purchase.productId GROUP BY client.id

My output is:

id  client_name   product       amount
1       A          Sweet         5500
2       B          Apple         2500
3       c          Mango         3500
4       D          Banana        4500

But I want output like where is the amount of each client purchased

Desired Output:

id  client_name  Apple  Banana Mango Sweet
1       A         x        x     x     x
2       B         X        x     x     x
3       c         X        x     x     x 
4       D         x        x     x     x

How can I do that with query. thanks

You need to do an "unpivot". Something like https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table — Daniel Gale, Aug 22 '18 at 17:44
This transformation is called pivoting and has been asked and answered here on SO soooo many times (pretty much on a daily basis). Answers to the linked duplicate question demonstrate how to perform this transformation in mysql. However, pls note that it may be a lot more effective to do the pivoting in the application code as opposed to mysql. — Shadow, Aug 22 '18 at 22:12

score 2 · Answer 1 · answered Aug 22 '18 at 17:43

If you have a limited products then i would use conditional aggregation :

SELECT c.id, c.client_name,
       SUM(CASE WHEN pd.id = 1 THEN p.Amount ELSE 0 END) AS Apple,
       SUM(CASE WHEN pd.id = 2 THEN p.Amount ELSE 0 END) AS Banana,
       SUM(CASE WHEN pd.id = 3 THEN p.Amount ELSE 0 END) AS Mango,
       SUM(CASE WHEN pd.id = 6 THEN p.Amount ELSE 0 END) AS Sweet
FROM client c INNER JOIN 
     purchase p 
     ON c.id = p.clientId INNER JOIN 
     product pd 
     ON pd.id = p.productId 
GROUP BY c.id, c.client_name;

Yeah. It's working well. You saved my time. Thanks. What would be then optimal solution the products is unlimited? — Mahmud, Aug 22 '18 at 18:10

score 1 · Answer 2 · answered Aug 22 '18 at 17:52

The easiest way if your Purchase table is static

select 
    c.id,
    c.name,
    (select SUM(p.amount) from Purchase p where p.productId = 1 and p.clientId = c.id) as Apple,
    (select SUM(p.amount) from Purchase p where p.productId = 2 and p.clientId = c.id) as Banana,
    (select SUM(p.amount) from Purchase p where p.productId = 3 and p.clientId = c.id) as Mango,
    (select SUM(p.amount) from Purchase p where p.productId = 6 and p.clientId = c.id) as Sweet
from Client c

Find all individual item purchase by all user in mysql

2 Answers2