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.

You say Microsoft SQL, and tag and . Which dbms are you actually using? — jarlh, Aug 14 '20 at 09:25
Show us some sample table data and the expected result - as formatted text (no images). A.k.a.[mcve] — jarlh, Aug 14 '20 at 09:28
The picture for the query is from a MS SQL interface, so I think it may have been incorrectly tagged — MTay, Aug 14 '20 at 09:28
@Se0ng11 like this? the count function need an argument it says. select () from CSO_EMP_ORG_DPM_VIE group by EXT_KEY having COUNT () >1 — Henk Hadders, Aug 14 '20 at 09:36
Sorry, but this paragraph: *'This is working so far, but when it has duplicate keys (numbers) in the column, I want it to generate it with 0 zero, and not nothing.'* is confusing. Could you clarify or give an example [in your question](https://stackoverflow.com/posts/63410056/edit), please? — Paul, Aug 14 '20 at 09:47
@Paul My expected result is; when two keys are the same, I want the query generate a 1. When no keys are the same, I want the query to generate an 0.The query is only working when i got dubble ext keys, — Henk Hadders, Aug 14 '20 at 09:50
But right now it generates a list of duplicate keys. So it makes no sense to, if that list is empty, say you want a zero instead. — underscore_d, Aug 14 '20 at 09:50
@underscore, correct, when the list is empty i want a zero instead — Henk Hadders, Aug 14 '20 at 09:52
@paul, i tested it with 3 same ext keys, my output is 1, screenshot added — Henk Hadders, Aug 14 '20 at 09:56
I've posted an answer - you may find that part of it is appropriate for you. The first answer could be used to give a result if you're simply checking to see if there are *any* duplicates whatsoever, but the first SQL example will tell you the counts of all distinct keys. — Paul, Aug 14 '20 at 10:10
when u say if have more than 1, then u want to return 0, right? then `select 0 from table ... where count(1)> 1`, this will ensure when it find record count more than 1, it will return 0, else will be null — Se0ng11, Aug 14 '20 at 10:30

forpas · Accepted Answer · 2020-08-14T10:28:48.930

2

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

edited Aug 14 '20 at 10:28

answered Aug 14 '20 at 10:11

forpas

160,666
10
38
76

That looks considerably smoother than my version! ;o) – Paul Aug 14 '20 at 10:15
thank you for the query, it show me now the complete list with all keys and 0. i only need one result 0 for the complete list. like in the screenshot "output with 3 same keys" – Henk Hadders Aug 14 '20 at 10:19
@HenkHadders check my 2nd query. – forpas Aug 14 '20 at 10:29
checked and works, thank you for your help, helps me al lot :-) – Henk Hadders Aug 14 '20 at 10:31

Paul · Answer 2 · 2020-08-14T10:35:41.870

0

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.

edited Aug 14 '20 at 10:35

answered Aug 14 '20 at 09:43

Paul

4,160
3
30
56

Hi Paul, thank you for your quick reply, my expected result needs to be a single 0 with no ext_keys who are the same. When ext_keys are the same i recieve a single 1 like in the screenshot. Unfortunately is the query not giving a 0 when no dubble entrys are in the table.I only need one result number, that would be a 0 or 1. no dubble keys or dubble keys – Henk Hadders Aug 14 '20 at 10:09
@HenkHadders - Do you not care which key is being repeated? – Paul Aug 14 '20 at 10:12
no not necessary, it is for our monitoring. After a 1 is given I will check the list for further analyse – Henk Hadders Aug 14 '20 at 10:17
got the sql working from forpas, thank you for your support :-) – Henk Hadders Aug 14 '20 at 10:32

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

2 Answers2