Fetch data from same table using two group by 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. ie In my table user type is individual and group(I,G) with 10 entry. 4 are individual and 6 are group. 2 individuals are registered in july and 2 are in august. 4 group members registred in july and 2 in august. So my output is

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 individual 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

I see the word "count" often. Use MySQL's aggregate COUNT() function, and there's also SUM() being another aggregate function. — Funk Forty Niner

Malovich Malovich · Accepted Answer · 2014-08-12T18:03:55

SELECT 
    COUNT(i.id) AS individual_count,
    COUNT(g.id) AS Group_count,
    DATE_FORMAT(u.joined_date, '%M') AS `Month`
FROM usertable AS u
LEFT JOIN usertable AS i ON u.id= i.id AND i.user_type = 'I'
LEFT JOIN usertable AS g ON u.id= g.id AND g.user_type = 'G'

GROUP BY DATE_FORMAT(u.joined_date, '%M')

That should get you the results you're looking for.

Fetch data from same table using two group by in mysql

Sample table Data

usertable

expected result

2 Answers