Skip to content
Advertisement

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)

Advertisement

Answer

Use a subquery:

select count(*)
from (select quantity, count(*) as cnt
      from t
      group by quantity
     ) x
where cnt = 1;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement