Get number of values that only appear once in a column

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.

Answer

Assuming your table is called T and your field is called F:

SELECT COUNT(F)
FROM (
    SELECT F
    FROM T
    GROUP BY F
    HAVING COUNT(*) = 1
) AS ONLY_ONCE

Advertisement

Answer