Q:

SQL query for displaying records on Highcharts graph

I am working on to achieve dynamic Highcharts graph – basic column and I need help in making SQL in MySQL. I need results for last the 12 months (irrespective of any data for the month (it can be 0 – but all the 12 months records should be fetched)) from the current month showing how many members (4 types of users) have registered on the site for the particular month.
There are 4 types of users:

Agents
Individuals
Builders
Real Estate Companies

For Months column it should retrieve last 12 months from current month - Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul. 
I have tried with the following query:
SELECT
CASE WHEN u.`userTypeID`=1 THEN COUNT(`userTypeID`) ELSE 0 END AS agent,
CASE WHEN u.`userTypeID`=2 THEN COUNT(`userTypeID`) ELSE 0 END AS individuals,
CASE WHEN u.`userTypeID`=3 THEN COUNT(`userTypeID`) ELSE 0 END AS builders,
CASE WHEN u.`userTypeID`=4 THEN COUNT(`userTypeID`) ELSE 0 END AS real_estate_companies,
 u.`userRegistredDate` AS 'timestamp'
FROM `dp_users` AS u
LEFT JOIN `dp_user_types` AS ut ON u.`userTypeID` = ut.`type_id`
WHERE u.`userRegistredDate` < Now( )
AND u.`userRegistredDate` > DATE_ADD( Now( ) , INTERVAL -12 MONTH )
GROUP BY DATE_FORMAT( u.`userRegistredDate`, '%b' )

Output (incorrect):
| AGENT | INDIVIDUALS | BUILDERS | REAL_ESTATE_COMPANIES |                   TIMESTAMP |
----------------------------------------------------------------------------------------
|     0 |           0 |        9 |                     0 | July, 01 2013 17:14:35+0000 |
|     3 |           0 |        0 |                     0 |  May, 15 2013 14:14:26+0000 |

Output (required: correct):
| AGENT | INDIVIDUALS | BUILDERS | REAL_ESTATE_COMPANIES |                   TIMESTAMP |
----------------------------------------------------------------------------------------
|     3 |           2 |        2 |                     2 | July, 01 2013 17:14:35+0000 |
|     1 |           2 |        0 |                     0 |  May, 15 2013 14:14:26+0000 |

Another way I tried was with sub-query, please find both examples links below:
http://sqlfiddle.com/#!2/ed101/53
http://sqlfiddle.com/#!2/ed101/54
Hoping to find favorable solution, thanks.

A:

Try this
    SELECT month(u.`userRegistredDate`),
sum(CASE WHEN u.`userTypeID`=1 THEN 1 ELSE 0 END) AS agent,
sum(CASE WHEN u.`userTypeID`=2 THEN 1 ELSE 0 END) AS individuals,
sum(CASE WHEN u.`userTypeID`=3 THEN 1 ELSE 0 END) AS builders,
sum(CASE WHEN u.`userTypeID`=4 THEN 1 ELSE 0 END) AS real_estate_companies,
 u.`userRegistredDate` AS 'timestamp',m.month
FROM (
SELECT 'January' AS
MONTH
UNION SELECT 'February' AS
MONTH
UNION SELECT 'March' AS
MONTH
UNION SELECT 'April' AS
MONTH
UNION SELECT 'May' AS
MONTH
UNION SELECT 'June' AS
MONTH
UNION SELECT 'July' AS
MONTH
UNION SELECT 'August' AS
MONTH
UNION SELECT 'September' AS
MONTH
UNION SELECT 'October' AS
MONTH
UNION SELECT 'November' AS
MONTH
UNION SELECT 'December' AS
MONTH
) AS m
left join `dp_users` AS u  ON m.month = MONTHNAME(u.`userRegistredDate`) and u.`userRegistredDate` < Now( )
AND u.`userRegistredDate` > DATE_ADD( Now( ) , INTERVAL -12 MONTH )
LEFT JOIN `dp_user_types` AS ut ON u.`userTypeID` = ut.`type_id`

GROUP BY  m.month
order by FIELD(m.month,'July','August','September','October','November','December','January','February','March','April','May','June')

Please check out this link

