Where or Having clause and COUNT(1)
I'm having trouble trying to figure out the way to get the conditional difference between two counts in a where clause.
I would like to scan a table and only select records where the record count of one column attribute is different than another.
I have
select name, count(1) from skill <<< TOTAL_COUNT
and
select name, count(1) from skill where score is not NULL <<<< SCORED_COUNT
I want to have a statement that just returns the names for those where TOTAL_COUNT - SCORED_COUNT = 0
... meaning those names where any of the rows have not been scored.
I've tried a "having clause" but it still returns all there names. Even those there TOTAL_COUNT - SCORED_COUNT > 0
Here's the non-working code....
select * from
(select full_name
from emp e
where e.manager = 'Smith, John'
having
((select count(1)
from emp_skill es
where es.emp_fk = e.id
group by null) -
(select count(1)
from emp_skill es1
where es1.emp_fk = e.id and
es1.self_prof is not NULL
group by null)) = 0
)