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;