Left Join with Count and Where Clause on right side

Question

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?

Mr. Bhosale · Accepted Answer · 2016-09-30T07:37:19.827

2

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

edited Sep 30 '16 at 07:37

answered Sep 30 '16 at 04:12

Mr. Bhosale

3,018
1
17
34

Warning: This query includes non-aggregate columns in the `SELECT` clause, and will not even run on the latest version of MySQL (or most other RDBMS). – Tim Biegeleisen Sep 30 '16 at 07:25
Its MS - SQL Syntax ( SQL_ SERVER ). not MYSQL..also refer http://stackoverflow.com/questions/5920070/why-cant-you-mix-aggregate-values-and-non-aggregate-values-in-a-single-select – Mr. Bhosale Sep 30 '16 at 07:29
This won't run on SQL Server to be certain. You can't select `groupName` because it is not an aggregate and does not appear in the `GROUP BY` list. – Tim Biegeleisen Sep 30 '16 at 07:33
Query Edited check now. Thank you – Mr. Bhosale Sep 30 '16 at 07:37

score 0 · Answer 2 · answered Sep 30 '16 at 04:21

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

Left Join with Count and Where Clause on right side

2 Answers2