CODEWITHSUNDEEP
mysqlsql
user2636749
user2636749

Reputation: 43

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:

  1. Agents
  2. Individuals
  3. Builders
  4. 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.

Upvotes: 4

Views: 929

Answers (3)

Pramod
Pramod

Reputation: 1041

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

Upvotes: 1

Organ
Organ

Reputation: 453

This is 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

Upvotes: 0

Sunil Tandon
Sunil Tandon

Reputation: 151

I think you are missing the group by clause on u.userTypeID column. Try by putting the group by clause on the 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

Upvotes: 0

Related Questions