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 onc开发者_如何学Pythone 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 F:

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

---

select count(*) from 
(
  select
    col1, count(*)
  from 
    Table
  group by 
    Col1
  Having 
    Count(Col1) = 1
)

---

just nest it a little...

select count( cnt ) from
( select count(mycol) cnt from mytab group by mycol )
where cnt = 1

---

select field1, count(field1) from my_table group by field1 having count(field1) = 1

select count(*) from (select field1, count(field1) from my_table group by field1 having count(field1) = 1)

first one will return the ones that are unique and second one will return the number of unique elements.

---

Could it be as simple as this:

Select count(*) From MyTable Group By MyColumn Where Count(MyColumn) = 1

---

This is what I did and it worked:

SELECT name 
FROM people JOIN stars ON stars.person_id = people.id 
JOIN movies ON movies.id = stars.movie_id
WHERE year = 2004
GROUP BY name, person_id ORDER BY birth;

note: I was working with several tables here.

CS50 Problem Set 7 (pset7) 9.sql fix!!