Month as columns query....tricky

[jwwceo]

Hello,

 

I am trying to write a report script that will take all my users, and tell me how many leads each has generated per month in a given year as well as the total per year.  So the ideal outcome would be a table with users as the rows, and 13 columns across the top, one for each month and one for the total.

 

The leads table has an investor ID field which matched the ID in the investor table, so the query needs to count the incidences of that ID when the date matches a certain month. The dates are in UnixTime stamp.

 

Here is what I have so far:

 

SELECT
COUNT( leads.investor ) as total,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'January' ) AS jan ,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'February') AS feb ,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'March' ) AS mar ,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'April' ) AS apr ,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'May' ) AS may ,
MONTHNAME( FROM_UNIXTIME( leads.date ) = 'June' ) AS jun ,
investors.first_name, investors.last_name, investors.investors_id
FROM leads, investors
WHERE YEAR( FROM_UNIXTIME( leads.date ) ) = '2010'
AND leads.investor = investors.investors_id
GROUP BY investors.investors_id
LIMIT 0 , 30 

 

I've only added 6 months while testing. The total part is working fine, for the whole year, but I am having a hard time getting the query to tally the leads by month. What am I missing here??

 

Thanks in advance!!

 

James

[dabaR]

Try...

 

SELECT

COUNT( leads.investor ) as total,

COUNT(CASE WHEN MONTHNAME( FROM_UNIXTIME( leads.date ) = 'January' ) THEN 1 ELSE NULL END) AS jan ,

...

investors.first_name, investors.last_name, investors.investors_id

FROM leads, investors

WHERE YEAR( FROM_UNIXTIME( leads.date ) ) = '2010'

AND leads.investor = investors.investors_id

GROUP BY investors.investors_id

LIMIT 0 , 30

[jwwceo]

Hmm....gettinng closer....but the query is still not adding the number of leads per month...this is what I am getting now...

 

total 	jan 	feb 	mar 	apr 	may 	jun 	first_name 	last_name 	investors_id
1 	0 	0 	0 	0 	0 	0 	xxx 	        xxx 	        5
3 	0 	0 	0 	0 	0 	0 	Billy 	        Bob 	        112

[dabaR]

OK, I did not feel like looking up the syntax for that monthname you are using, but I bet now that that is where your problem lies, so here it is:

 

SELECT
COUNT( leads.investor ) as total,
COUNT(CASE WHEN MONTHNAME( FROM_UNIXTIME( leads.date ) ) = 'January' THEN 1 ELSE NULL END) AS jan ,
...
investors.first_name, investors.last_name, investors.investors_id
FROM leads, investors
WHERE YEAR( FROM_UNIXTIME( leads.date ) ) = '2010'
AND leads.investor = investors.investors_id
GROUP BY investors.investors_id
LIMIT 0 , 30 

[jwwceo]

That works. Just off by one parenthesis. Thanks a million dude!!!

 

JAmes