I have 4 tables:
users
id | name |
---|---|
1 | a |
2 | b |
products
id | name |
---|---|
1 | aC |
2 | bC |
bought
id | user_id | product_id | amount |
---|---|---|---|
1 | 1 | 1 | 100 |
2 | 2 | 1 | 200 |
sold
id | user_id | product_id | amount |
---|---|---|---|
1 | 1 | 1 | 100 |
2 | 2 | 2 | 200 |
Report Now I want to have a query to get below report:
user_id | user_name | bought_aC | bought_bC | sold_aC | sold_bC |
---|---|---|---|---|---|
1 | a | 100 | |||
2 | b | 100 | 200 |
Description
I want to have list of users with bought and sold amount of products.
Each user has one row in report. each product has two column for one for bought , one for sold.
My products are limited, so I want to have different columns for each product(each product has bought and sold column that show amount)
Is this possible?
I would appreciate if any one help me.
Advertisement
Answer
You are aggregating along two dimensions, so you need to aggregate before joining:
x
select u.id, u.name,
b.bought_aC, b.bought_bC,
s.sold_aC, s.sold_bC
from users u left join
(select b.user_id,
sum(case when pb.name = 'aC' then b.amount end) as bought_aC,
sum(case when pb.name = 'bC' then b.amount end) as bought_bC
from bought b join
products pb
on b.product_id = pb.id
group by b.user_id
) b
on u.id = b.user_id left join
(select s.user_id
sum(case when ps.name = 'aC' then s.amount end) as sold_aC,
sum(case when ps.name = 'bC' then s.amount end) as sold_bC
from sold s join
products ps
on s.product_id = ps.id
group by s.user_id
) s
on u.id = s.user_id
order by u.id