Jump to content


Photo

Complex Query problem


  • Please log in to reply
2 replies to this topic

#1 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 04 June 2006 - 02:58 PM

FIXED![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
WHERE MONTH(`date`) = MONTH(CURDATE())-1
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
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.
Thanks
John
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#2 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 04 June 2006 - 03:58 PM

After all that it was just a simple query
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

Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 June 2006 - 05:04 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users