Fetch data from same table using two group by clauses in mysql

Question

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

Answer 1

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;