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

Question

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.

forpas · Accepted 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

Paul · Answer

It's not blindingly obvious as to what you are asking for.  To that end, this query gives a 1/0 result based on having a count greater than 0 for each key...
SELECT   
    p.EXT_KEY, 
    EXT_KEY_RESULT = ISNULL((SELECT 1 
        FROM CSO_EMP_ORG_DPM_VIE c
        WHERE c.EXT_KEY = p.EXT_KEY 
        HAVING COUNT(EXT_KEY) > 0), 0)
FROM 
    CSO_EMP_ORG_DPM_VIE p

Alternatively, if you are looking to count each of the keys, you could try...
SELECT   EXT_KEY, COUNT(EXT_KEY)
FROM     CSO_EMP_ORG_DPM_VIE
GROUP BY EXT_KEY

It's always good practice to specify a particular field in the COUNT aggregate, particularly the primary key, as it's faster to reference.
You really need to give us an expected result for your requirements and be very clear about your expectations.
SELECT CASE WHEN COUNT(EXT_KEY) > 0 THEN 1 ELSE 0 AS dupes
FROM CSO_EMP_ORG_DPM_VIE

PLEASE NOTE: Credit here to forpas for providing a smoother answer which I have borrowed.

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

2 Answers

Add your own answers!

Ask a Question