1

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

2
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.