From: Itai Zukerman <zukerman@math-hat.com>
To: pgsql-sql@postgresql.org
Subject: Aggregates and Primary Keys
Date: 27 Jul 2000 10:00:54 -0400
Message-ID: <87k8e78yg9.fsf@matt.w80.math-hat.com> (raw)
Hi,
I have this:
create table a ( x int4 primary key, dat int4, count int4 ) ;
create table b ( x int4 references a(x), count int4 ) ;
insert into a values ( 1, 1, 10 ) ;
insert into a values ( 2, 2, 20 ) ;
insert into b values ( 1, 2 ) ;
insert into b values ( 1, 3 ) ;
insert into b values ( 2, 3 );
insert into b values ( 2, 4 );
select * from a ;
select * from b ;
x | dat | count
---+-----+-------
1 | 1 | 10
2 | 2 | 20
(2 rows)
x | count
---+-------
1 | 2
1 | 3
2 | 3
2 | 4
(4 rows)
select a.x, a.dat, a.count - sum(b.count)
from a, b
where a.x = b.x
group by a.x, a.dat, a.count ;
x | ?column?
---+----------
1 | 5
2 | 13
(2 rows)
My concern is with the "group by" clause. Strictly speaking, it
shouldn't be necessary to *also* group by a.dat and a.count, since a.x
is a primary key, right? Is there some performance loss in specifying
a.dat and a.count in the group by? Should I be doing this some other
way?
Thanks,
-itai
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: zukerman@math-hat.com
Subject: Re: Aggregates and Primary Keys
In-Reply-To: <87k8e78yg9.fsf@matt.w80.math-hat.com>
* 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