How to get data from 2 tables in MYSQL


Problem :

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,
       o.member_id,
       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!



Solution :

You could use conditions in the SUM/COUNT functions.

SELECT 
  m.name,
  COUNT(o.id) AS number_of_order,
  SUM(o.total) AS total2,
  COUNT(IF(o.status=1, 1, NULL)) AS paid_order,
  COUNT(IF(o.status=2, 1, NULL)) AS unpaid_order,
  SUM(IF(o.status=1, o.total, 0)) AS paid,
  SUM(IF(o.status=2, o.total, 0)) AS unpaid
FROM orders o 
LEFT JOIN members m ON o.member_id=m.id
GROUP BY o.member_id

Mysql Tutorials

Mysql Howto..

how to sort varchar column containing numeric values with linq lambdas to Entity

How can i use function in my mysql query?

How to retrieve large data from mysql to php?

How can I increment a specific year using datetime datatype in mysql?

How to use the OR statement in MySQL while using the LIKE search statement

How to select a static value/string with SQL?

Wordpress: how to mass migrate posts from a category-ID to a taxonomy-TERM?

MySQL - How to create a child record under table2 whenever table1 is having a new record using MySQL workbench?

How to upgrade to a newest build version my installed MySQL on Debian?

How to improve Limit clause in MySQL

Is there a limit of how big MySQL table can get in order to be efficient assuming infinite disk space (but realistically high computing power) [closed]

How to retrieve data for a statistics page using MYSQL Queries

How to use a MySQL view and ORDER BY clause?

How to connect mysql database and Sybase Database in one connection string in Perl?

How do I handle image management (upload, removal, etc.) in CakePHP?

How to handle an array in a SQL field?

If I specify any value for an auto-increment field, how can I get the next or previous value?

How to display multiple records in java?

SQL function to get count of how many times string appears in column?

How to copy table between two models in Mysql workbench?

How to update mysql database, with a variable?

How to select multiple rows from mysql with one query and use them in php

How to get all data of a open api site

How to go about implementing prepared statements with parameters, is it even possible with this query?

How to combine sha1( column + php string) in mysql

How to set predefined value for a field in mysql

How to make a multiple column mysql fulltext search where partial words are matched

Show single set of results from two tables

How to store image into binary form and how to retrieve that back?

How to Share a MySQL connection between 2 different PHP Processes