Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: where not unique
Date: Fri, 12 Mar 2004 13:02:09 +0000
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200403121302.09243.gary.stainburn@ringways.co.uk>

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