How do you create a query to give you the count of 2 columns?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • AdamOnAccess
    New Member
    • Aug 2008
    • 99

    #1

    How do you create a query to give you the count of 2 columns?

    I'm having trouble getting the correct count from a total query. I have 2 tables in a 1-To-Many relationship and I want to count a column in the many table. This query works correctly:

    Code:
    SELECT
    tbmAdGroups.AdGroupID,
    tbmAdGroups.AdGroupName,
    Count(tbmKeyPhrases.KeyPhraseID) AS CountOfKeyPhraseID
    
    FROM tbmAdGroups
    LEFT JOIN tbmKeyPhrases 
    ON tbmAdGroups.AdGroupID = tbmKeyPhrases.AdGroupID
    
    WHERE tbmAdGroups.DestinationID=1
    
    GROUP BY tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName;
    Now I add another table related as another "many" to the same "one" table. I want to count another column on the "many" side. This query doesn't return the correct answer...

    Code:
    SELECT
    tbmAdGroups.AdGroupID,
    tbmAdGroups.AdGroupName,
    Count(tbmKeyPhrases.KeyPhraseID) AS CountOfKeyPhraseID, Count(tbmSeedPhrases.SeedPhrase) AS CountOfSeedPhrase
    
    FROM (tbmAdGroups LEFT JOIN tbmKeyPhrases ON tbmAdGroups.AdGroupID = tbmKeyPhrases.AdGroupID)
    RIGHT JOIN tbmSeedPhrases ON tbmAdGroups.AdGroupID = tbmSeedPhrases.AdGroupID
    
    WHERE tbmAdGroups.DestinationID=1
    
    GROUP BY tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName;
    The second query returns counts much greater than they should be. Can anyone see what I'm doing wrong?

    Thanks,
    Adam
Working...