CODEWITHSUNDEEP
sqlcount
half of a glazier
half of a glazier

Reputation: 2076

Count only non duplicate values

I need a query to count only values that aren't duplicates, is there any way to do this without subqueries or temp tables?

If I have a table like this:

+------------+----------+
| item_name  |quantity  |
+------------+----------+
| Calculator |       89 |
| Notebooks  |       40 |
| Pencil     |       40 |
| Pens       |       32 |
| Shirts     |       29 |
| Shoes      |       29 |
| Trousers   |       29 |
+------------+----------+

I can't use SELECT COUNT(DISTINCT quantity) because it returns 4. (89 | 40 | 32 | 29)

How can I return 2? (89 | 32)

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use a subquery:

select count(*)
from (select quantity, count(*) as cnt
      from t
      group by quantity
     ) x
where cnt = 1;

Upvotes: 1

Related Questions