SQL query to display records in the Highcharts column

Question

I am working to achieve the Highcharts dynamic graph - the base column, and I need help creating SQL in MySQL. I need results for the last 12 months (regardless of any data for the month (maybe 0 - but all 12-month records should be received)) from the current month, showing how many participants (4 types of users) registered on the site for a particular month.

There are 4 types of users:

• Agents
• Individuals
• Builders
• Real Estate Companies

For the Months column, it should get the last 12 months from the current month - August, September, October, November, December, January, February, March, April, May, June, July.

I 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 (wrong):

 | 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 a subquery, please find both links below:

http://sqlfiddle.com/#!2/ed101/53 http://sqlfiddle.com/#!2/ed101/54

Hoping to find a favorable solution, thanks.

Answer 1

try it

  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 read this link.

Answer 2

I think you are missing the group by clause in the u column. userTypeID . Try putting the group by clause in a specific column. I think you are looking for a query like this:

 select max (A) A, max(B) B, max (C) C, max(D) D, max (E) date from ( select distinct case when t1.usertype=1 then count(t2.username) else 0 end as "A", case when t1.usertype=2 then count(t2.username) else 0 end as "B", case when t1.usertype=3 then count(t2.username) else 0 end as "C", case when t1.usertype=4 then count(t2.username) else 0 end as "D", month(u.`userRegistredDate`) E from #tab1 t1 left join #tab2 t2 on t1.usertype = t2.usertype group by month(u.`userRegistredDate`), t1.usertype ) tab group by E 

Answer 3

Is this what you want:

 SELECT 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' 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' ) 

http://sqlfiddle.com/#!2/ed101/69