Najji Muhammed Najji Muhammed - 1 year ago 50
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download