Mysql – Fetch data from same table using two group by clauses in thesql

aggregategroup byMySQL

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month.

In my table, user_type is individual and group (values I,G) with 10 entries.

  • 4 are individual and 6 are group
  • 2 individuals are registered in July and 2 are in August
  • 4 group members registered in July and 2 in August.

So my output should be the following:

  1. count of individual register in month of July ?
  2. count of group register in month of July ?
  3. count of individual register in month of August ?
  4. count of group register in month of August ?

Sample table Data

usertable

+----+--------+-----------+---------------------+
| id | Name   | user_type | joined_date         |
+----+--------+-----------+---------------------+
| 1  | john   |  I        | 2014-07-16 00:00:00 |
| 2  | james  |  G        | 2014-07-11 00:00:00 |
| 3  | george |  I        | 2014-08-11 00:00:00 |
| 4  | aby    |  I        | 2014-07-11 00:00:00 |
| 5  | padma  |  G        | 2014-08-11 00:00:00 |
| 6  | Mick   |  G        | 2014-08-16 00:00:00 |
| 7  | Bony   |  G        | 2014-07-21 00:00:00 |
| 8  | Sebas  |  I        | 2014-08-11 00:00:00 |
| 9  | danie  |  G        | 2014-07-11 00:00:00 |
| 10 | davi   |  G        | 2014-07-01 00:00:00 |
+----+--------+-----------+---------------------+

expected result

+------------------+-------------+--------+
| Individual_Count | Group_Count | Month  |
+------------------+-------------+--------+
| 2                | 4           | July   |
| 2                | 2           | August |
+------------------+-------------+--------+

I need to populate This date in a Bar chart. I am bit confuse for this query for last days.Please help me if possible.
Thanks in advance

Best Answer

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    DATE_FORMAT(dt,'%M')       "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    DATE_FORMAT(dt,'%M') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

If you would like the counts summed up, include WITH ROLLUP

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
Related Question