I am in desperate need for help.
I am trying to create a page with a top 20 chart.
The problem I am having is that I can't get the COUNTS for last month and this months
ordered by the position this month descending.
The fields I am using are :
Table --> bfpomerchants
[blockquote]Appropriate fields --> companyID (INT) & company (VARCHAR)[/blockquote]
Table --> click_log
[blockquote]Appropriate fields --> clickID (INT), date (timestamp) & companyID (INT)[/blockquote]
I have tried all sorts of JOINS, COUNTS and every other method I know of.
I have figured out the
WHERE MONTH(`date`) = MONTH(CURDATE())-1for last month, and without the -1 for this month.
I can get the values for last month and this month from seperate statements,
but I need them in a seperate statement
This is what I'm using to get them seperately
SELECT COUNT(c.clickID) AS lastMonth, b.company FROM bfpomerchants b RIGHT JOIN click_log c USING (companyID) WHERE MONTH(`date`) = MONTH(CURDATE())-1 AND b.company IS NOT NULL GROUP BY b.company ORDER BY lastMonth DESC;
I have tried doing it myself but ended up with a statement that didn't work and was taking up to
and just over 180 seconds.
Can anybody help me, if you need any info on the field structure then just ask.