CODEWITHSUNDEEP
mysqlsql
Clinton J
Clinton J

Reputation: 2123

Left Join with Count and Where Clause on right side

I have the following query:

SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G 
    LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
    WHERE C.cdCode IN 
        (SELECT cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode

In this instance it does not show the group if the count associated is 0. I want this to show every element from musicalgroup even if the count is 0. When I do this:

 SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G 
     LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
     GROUP BY G.groupCode

It shows even if the count is 0, but I need to only show CDs with a rating under 10. How would I accomplish both of those goals in one query?

Upvotes: 1

Views: 526

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Try this:

SELECT G.groupCode,
       G.groupName,
       COUNT(C.cdCode) AS numberOfTops10CDs 
FROM musicalgroup G 
LEFT OUTER JOIN cd C
    ON C.groupCode = G.groupCode AND
       C.cdCode in (SELECT distinct cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode,G.groupName

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

I think something like this is along the lines of what you are trying to do. The query below uses conditional aggregation to count the number of CDs in each musical group which have a rating greater than 10. It avoids the problem of using a WHERE clause, which can remove records you want to use for the result set.

SELECT g.groupCode,
       SUM(CASE WHEN c.cdCode IN
           (SELECT cdCode FROM topcds WHERE rating > 10) THEN 1 END) AS numberOfTops10CDs
FROM musicalgroup g
LEFT JOIN cd c
    ON g.groupCode = c.groupCode
GROUP BY g.groupCode

Upvotes: 0

Related Questions