Firstly, if it is relevant, I’m using MySQL, though I assume a solution would work across DB products. My problem is thus:
I have a simple table with a single column. There are no constraints on the column. Within this column there is some simple data, e.g.
a a b c d d
I need to get the number/count of values that only appear once. From the example above that would be 2 (since only b and c occur once in the column).
Hopefully it’s clear I don’t want DISTINCT values, but UNIQUE values. I have actually done this before, by creating an additional table with a UNIQUE constraint on the column and simply INSERTing to the new table from the old one, handling the duplicates accordingly.
I was hoping to find a solution that did not require the temporary table, and could somehow just be accomplished with a nifty SELECT.
Assuming your table is called
T and your field is called
SELECT COUNT(F) FROM ( SELECT F FROM T GROUP BY F HAVING COUNT(*) = 1 ) AS ONLY_ONCE