From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: where not unique
Date: Fri, 12 Mar 2004 13:02:09 +0000
Message-ID: <200403121302.09243.gary.stainburn@ringways.co.uk> (raw)
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).
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
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: gary.stainburn@ringways.co.uk
Subject: Re: where not unique
In-Reply-To: <200403121302.09243.gary.stainburn@ringways.co.uk>
* 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