How to find PolicyNumber that have more than one DIFFERENT SICCode from the same source table

Question

I have table with PolicyNumber and SICCode.

Typically each PolicyNumber has only one unique SICCode. But I want to check, to make sure the data is correct. How would I do that?

The query below gives me the number of SICCode's each PolicyNumber has, but its not what I need. I want to identify what PolicyNumber (if any) has more than one DIFFERENT SICCodes.

select  PolicyNumber, 
        count(SICCode) as count  
from    PlazaInsuranceWPDataSet 
group by PolicyNumber 
having count(SICCode)>1

Answer 1

Use COUNT(DISTINCT SICCode):

Select  PolicyNumber,
        Count(Distinct SICCode) As count
From    PlazaInsuranceWPDataSet
Group By PolicyNumber
Having  Count(Distinct SICCode) > 1;