Return a 0 if no rows are found in Microsoft SQL Server

I need your help with this query.

My table CSO_EMP_ORG_DPM_VIE has a column with different keys. Column name is EXT_KEY.

When I receive the same key number in EXT_KEY, I want the SQL code to count the duplicates using this query:

select EXT_KEY
from CSO_EMP_ORG_DPM_VIE
group by EXT_KEY
having count(*) > 1

This is working so far, but when it has no duplicate keys (numbers) in the column, I want it to generate it with 0 zero, and not nothing.

My expected result is; when two keys are the same I want to generate a 1. When no keys are the same, I want to generate an 0. Right now i got no result at all like in the screenshot.

How can I fix this SQL query accordingly?

Thank you in advance.

Answer

Use a CASE expression like this:

SELECT EXT_KEY,
       CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END flag
FROM CSO_EMP_ORG_DPM_VIE
GROUP by EXT_KEY

or if you want 1 result for the table:

SELECT CASE WHEN COUNT(EXT_KEY) > COUNT(DISTINCT EXT_KEY) THEN 1 ELSE 0 END flag
FROM CSO_EMP_ORG_DPM_VIE