grandadevans Posted April 23, 2006 Share Posted April 23, 2006 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 cat4FROM bfpomerchantsWHERE (bfpomerchants.cat1ID = '%s') OR (bfpomerchants.cat2ID = '%s') OR (bfpomerchants.cat3ID = '%s') OR (bfpomerchants.cat4ID = '%s') [/code] Quote Link to comment https://forums.phpfreaks.com/topic/8183-count-and-group-by-help/ Share on other sites More sharing options...
fenway Posted April 23, 2006 Share Posted April 23, 2006 Couldn't you just add...[code] (SELECT COUNT(*) FROM click_log WHERE bfpomerchants.companyID = companyID ) AS click_count[/code]To your query? Quote Link to comment https://forums.phpfreaks.com/topic/8183-count-and-group-by-help/#findComment-29863 Share on other sites More sharing options...
grandadevans Posted April 23, 2006 Author Share Posted April 23, 2006 God Damnit!I could have sworn that I had tried that.What a day 1, week 1 schoolboy error.Cheers mateBy the way.How do I get the Fixed thing on the title? Quote Link to comment https://forums.phpfreaks.com/topic/8183-count-and-group-by-help/#findComment-29865 Share on other sites More sharing options...
fenway Posted April 24, 2006 Share Posted April 24, 2006 Glad you got it working; I don't think you can change the title of a post, can you? Quote Link to comment https://forums.phpfreaks.com/topic/8183-count-and-group-by-help/#findComment-30002 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.