Najji Muhammed Najji Muhammed - 7 months ago 8
SQL Question

SQL - Using count to ONLY count for chosen values

select s.code, s.id
from subjects s
join courses c on (c.subject = s.id)
group by s.id
having count(*) > 25
except
select s.code, s.id
from subjects s
join courses c on (s.id = c.subject)
join course_staff cs on (cs.course = c.id);


This returns the result I want except the
select s.code, s.id
line should really be
select s.code, count(*)
. I'm just using it as a test. When I use the code in the example I get 4 tuples as expected but when I switch out the
s.id
for
count(*)
I get ALL of tuples with count > 25 from the subject table. Is there a way to ONLY display count results for which the code in the example returns?

Answer

Try something like this

select s.code, count(*)
from subjects s
join courses c on (c.subject = s.id)
left join 
(select s.code, s.id
    from subjects s
    join courses c on (s.id = c.subject)
    join course_staff cs on (cs.course = c.id)) a
on a.code=s.code and a.id = s.id
where a.code is null
group by s.id
having count(*) > 25

The left join of two tables (t1,t2) with null check on column of t2, will select all rows of table t1 where the rows didnt match with table t2.