sqlsql-serversqlhelper

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.

No results in the query

changed having count to 0 for results

output with 3 same keys

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.

Solution

  • 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