Jump to content

Complex Query problem


grandadevans

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
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.