...

View Full Version : Select from table where count == N for each distinct row



Stooshie
05-05-2011, 11:57 AM
Hi, I'll try and make this as clear as I can.

If I have a table (t) with a column (c) How do I display a list of c values where that value only exists once in the table.

I have tried:



SELECT
t.c,
count(t.c) AS num
FROM
t
GROUP BY
t.c
WHERE
num = 1

but I receive an error message saying the WHERE is an invalid use of the GROUP BY clause.

Any ideas anyone?

Andrew.

Stooshie
05-05-2011, 12:07 PM
It's OK, I've got it.



SELECT
t.c,
count(t.c) AS num
FROM
t
GROUP BY
t.c
HAVING
count(t.c) = 1

I had totally forgotten about the HAVING clause. :-)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum