Mysql : get data from 2 tables (need help)
- by quangtruong1985
Assume that I have 2 tables : members and orders (Mysql)
Members :
id | name
1 | Lee
2 | brad
Orders :
id | member_id | status (1: paid, 2: unpaid) | total
1 | 1 | 1 | 1000000
2 | 1 | 1 | 1500000
3 | 1 | 2 | 1300000
4 | 2 | 1 | 3000000
5 | 2 | 2 | 3500000
6 | 2 | 2 | 3300000
I have a sql query :
SELECT m.name, COUNT(o.id) as number_of_order, SUM(o.total) as total2 FROM orders o LEFT JOIN members m ON o.member_id=m.id GROUP BY o.member_id
which give me this:
name | number_of_order | total2
Lee | 3 | 3800000
brad | 3 | 9800000
All that I want is something like this :
name | number_of_order | total2
| Paid Unpaid | Paid Unpaid
------------------------------------------------
Lee | 3 | 3800000
| 2 1 | 2500000 1300000
------------------------------------------------
brad | 3 | 9800000
| 1 2 | 3000000 6800000
------------------------------------------------
How to make a query that can give me that result?
Thanks for your time!
