Re: where not unique

Gary Stainburn wrote:

>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.
>   I think that this is not the best way, but :

select * from (select registration, count(registration) as counter from 
stock group by registration)
where counter > 1;

>
>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=#
>  
>   This is because the subselect is returning 2 columns but "in clause" 
is expecting just one column.
Try to use "exists" instead of "in", ok ?

>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).
>  
>
   I hope this helps you.