pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere not unique
4+ messages / 3 participants
[nested] [flat]
* where not unique
@ 2004-03-12 13:02 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Gary Stainburn @ 2004-03-12 13:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where not unique
@ 2004-03-12 13:54 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 4+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-12 13:54 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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.
>
I think that this is not the best way, but :
select * from (select registration, count(registration) as counter from
stock group by registration)
where counter > 1;
>
>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=#
>
>
This is because the subselect is returning 2 columns but "in clause"
is expecting just one column.
Try to use "exists" instead of "in", ok ?
>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).
>
>
I hope this helps you.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where not unique
@ 2004-03-12 13:57 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Paul Thomas @ 2004-03-12 13:57 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: 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 |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where not unique
@ 2004-03-12 14:05 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 0 replies; 4+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-12 14:05 UTC (permalink / raw)
To: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>; pgsql-sql
You're right, Reinoud. Sorry for my mystake...
Reinoud van Leeuwen wrote:
>On Fri, Mar 12, 2004 at 10:54:25AM -0300, Daniel Henrique Alves Lima wrote:
>
>
>>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.
>>>
>>>
>>>
>> I think that this is not the best way, but :
>>
>>select * from (select registration, count(registration) as counter from
>>stock group by registration)
>>where counter > 1;
>>
>>
>
>Isn't that the same as
>
>select registration
>from stock
>group by registration
>having count (registration) > 1;
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-12 14:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-12 13:02 where not unique Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-03-12 13:54 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-12 14:05 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-12 13:57 ` Paul Thomas <paul@tmsl.demon.co.uk>
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