Mysql : get data from 2 tables (need help)

Posted by quangtruong1985 on Stack Overflow See other posts from Stack Overflow or by quangtruong1985
Published on 2012-08-30T03:25:53Z Indexed on 2012/08/30 3:38 UTC
Read the original article Hit count: 359

Filed under:
|
|
|
|

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!

© Stack Overflow or respective owner

Related posts about mysql

Related posts about database