DISTINCT AND COUNT(*)=1 not working on SQL

Question

I need to show the ID (which is unique in every case) and the name, which is sometimes different. In my code I only want to show the names IF they are unique.

I tried with both distinct and count(*)=1, nothing solves my problem.

SELECT DISTINCT id, name
FROM person
GROUP BY id, name
HAVING count(name) = 1;

The result is still showing the names multiple times

You got `DISTINCT` wrong. The way you use it, it applies to the whole row, not just `id`. — The Impaler, Oct 14 '19 at 13:45

ravioli · Answer 1 · 2019-10-14T14:06:13.280

2

If your DBMS supports it, you can use a window function:

SELECT id, name
FROM (
  SELECT id, name, COUNT(*) OVER(PARTITION BY name) AS NameCount -- get count of each name
  FROM person
) src
WHERE NameCount = 1

If not, you can do:

SELECT id, name
FROM person
WHERE name IN (
  SELECT name
  FROM person
  GROUP BY name
  HAVING COUNT(*) = 1 -- Only get names that occur once
)

edited Oct 14 '19 at 14:06

answered Oct 14 '19 at 13:49

ravioli

3,749
3
14
28

Shouldn't it be `PARTITION BY id`? – The Impaler Oct 14 '19 at 13:51
1

Why? You want to group them by `name` and count the number of rows in each group. – ravioli Oct 14 '19 at 13:54

score 2 · Answer 2 · answered Oct 14 '19 at 13:53

By "unique", I assume you mean names that only appear once. That is not what "distinct" means in SQL; the use of distinct is to remove duplicates (either for counting or in a result set).

If so:

SELECT MAX(id), name
FROM person
GROUP BY name
HAVING COUNT(*) = 1;

DISTINCT AND COUNT(*)=1 not working on SQL

2 Answers2