Re: where not unique

On 12/03/2004 13:02 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.
> 
> 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)
If I understand you correctly, you've got something like

  mytable
stockno  regno
--------------
SN1      REG1
SN2      REG2
SN3      REG3
SN4      REG2

and you want to list REG2. Something like

select regno from mytable group by regno having count(stockno) > 1;

might do it.

HTH

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+