where not unique

Hi folks,

I've got a table which contains amongst other things a stock number and a
registration number. I need to print out a list of stock number and reg
number where reg number is not unique (cherished number plate transfer not
completed). I've tried variations of a theme based on

select stock_number, registration from stock where registration in
(select registration, count(registration) as count
from stock where count > 1 group by registration);

but I have two problems. Firstly with the sub-select I get:

usedcars=# select registration, count(registration) as count from stock where
count > 1 group by registration;
ERROR: Attribute 'count' not found
usedcars=#

although if I miss out the where clause I get the expected results.

Secondly, when I run the full query I get:

usedcars=# select stock_number, registration from stock
usedcars-# where registration in
usedcars-# (select registration, count(registration) as count from stock group
by registration);
ERROR: Subselect has too many fields
usedcars=#

which is obviously because of the count field.

Can anyone tell me where I'm going wroing with these count fields?
(I've tried renaming the field to regcount in case it was a reserved word
problem).
--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000