Date: Fri, 12 Mar 2004 13:57:00 +0000
From: Paul Thomas <paul@tmsl.demon.co.uk>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: "pgsql-sql @ postgresql . org" <pgsql-sql@postgresql.org>
Subject: Re: where not unique
Message-ID: <20040312135700.A19064@bacon>
References: <200403121302.09243.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
In-Reply-To: <200403121302.09243.gary.stainburn@ringways.co.uk>;
	from gary.stainburn@ringways.co.uk on Fri, Mar 12, 2004 at
	13:02:09 +0000
Lines: 68


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