Aim: To find all properties where all ratings are not A to D
Issue: If a property has multiple ratings which are not A - D then these are still counted.
Code:
Select DISTINCT (PropertyID)
FROM TableA
WHERE Rating NOT BETWEEN 'A' AND 'D'
Select PropertyID
FROM TableA
group by PropertyID
having sum(case when Rating IN ('A','B','C','D') then 1 else 0 end) = 0
If you want only the number of properties then put a count around that
select count(*) as property_count
from
(
select PropertyID from tableA ...
) alias_name
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments