Skip site navigation (1) Skip section navigation (2)

Re: where not unique

From: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
To: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Cc: "pgsql-sql (at) postgresql (dot) org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: where not unique
Date: 2004-03-12 13:57:00
Message-ID: 20040312135700.A19064@bacon (view raw or flat)
Thread:
Lists: pgsql-sql
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   |
+------------------------------+---------------------------------------------+

In response to

pgsql-sql by date

Next:From: Daniel Henrique Alves LimaDate: 2004-03-12 14:05:19
Subject: Re: where not unique
Previous:From: Daniel Henrique Alves LimaDate: 2004-03-12 13:54:25
Subject: Re: where not unique

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group