Jump to content

Archived

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

grandadevans

COUNT and GROUP BY help

Recommended Posts

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
[code]
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')
[/code]

Share this post


Link to post
Share on other sites
Couldn't you just add...

[code]    (SELECT COUNT(*) FROM click_log WHERE bfpomerchants.companyID = companyID ) AS click_count[/code]
To your query?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Glad you got it working; I don't think you can change the title of a post, can you?

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.