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 |
+------------------------------+---------------------------------------------+