Jump to content


Photo

COUNT and GROUP BY help


  • Please log in to reply
3 replies to this topic

#1 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 23 April 2006 - 03:42 PM

Hi all,
My head is about to explode. I have been trying to figure this out now for a few days.
The query below works fine but I have another field I need to add.
I have another table called click_log which logs the companyID clicked, what I need to do is add the number of cliks for that company to the query.
I have tried COUNT(click_log.companyID) in alsorts of variations but I cannot get it right.
Can anybody help. I have laid the code out as clear as I can below. If any more info is needed just give us a shout.
Cheers
SELECT 
    bfpomerchants.companyID, 
    bfpomerchants.company, 
    bfpomerchants.cat1ID, 
    bfpomerchants.cat2ID, 
    bfpomerchants.cat3ID, 
    DATE_FORMAT(bfpomerchants.date, '%%D %%b %%y') AS dateAdded, 
    LEFT(bfpomerchants.`desc`, 100) AS short, 
    bfpomerchants.URL, 
    bfpomerchants.company, 
    (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat1ID = bfpocats.catID ) AS cat1, 
    (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat2ID = bfpocats.catID ) AS cat2, 
    (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat3ID = bfpocats.catID ) AS cat3, 
    (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat4ID = bfpocats.catID ) AS cat4
FROM 
    bfpomerchants
WHERE 
    (bfpomerchants.cat1ID = '%s') OR 
    (bfpomerchants.cat2ID = '%s') OR 
    (bfpomerchants.cat3ID = '%s') OR 
    (bfpomerchants.cat4ID = '%s') 

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 fenway

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

Posted 23 April 2006 - 05:58 PM

Couldn't you just add...

    (SELECT COUNT(*) FROM click_log WHERE bfpomerchants.companyID = companyID ) AS click_count
To your query?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 23 April 2006 - 06:05 PM

God Damnit!
I could have sworn that I had tried that.
What a day 1, week 1 schoolboy error.
Cheers mate
By the way.
How do I get the Fixed thing on the title?
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>

#4 fenway

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

Posted 24 April 2006 - 07:07 AM

Glad you got it working; I don't think you can change the title of a post, can you?
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