Jump to content

Archived

This topic is now archived and is closed to further replies.

grandadevans

Complex Query problem

Recommended Posts

[b]FIXED![/b][size=1][color=#CC0000]
Hi,
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
[code]WHERE MONTH(`date`) = MONTH(CURDATE())-1[/code]
for 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
[code]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;[/code]

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.
Thanks
John

Share this post


Link to post
Share on other sites
After all that it was just a simple query
[code]SELECT bfpomerchants.companyID, bfpomerchants.company,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())-1) AS lastMonth,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())) AS thisMonth
FROM bfpomerchants
ORDER BY thisMonth DESC[/code]

Share this post


Link to post
Share on other sites
I was actually thinking that it was the fact that you couldn't use the index on `date` -- but if you got it working, then fine. Also, don't use RIGHT JOINs... ugh.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.