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
Date: Fri, 12 Mar 2004 13:57:00 +0000
Message-ID: <20040312135700.A19064@bacon> (raw)
In-Reply-To: <200403121302.09243.gary.stainburn@ringways.co.uk>
References: <200403121302.09243.gary.stainburn@ringways.co.uk>
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 |
+------------------------------+---------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul@tmsl.demon.co.uk, gary.stainburn@ringways.co.uk
Subject: Re: where not unique
In-Reply-To: <20040312135700.A19064@bacon>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox