From: | Itai Zukerman <zukerman(at)math-hat(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Aggregates and Primary Keys |
Date: | 2000-07-27 14:00:54 |
Message-ID: | 87k8e78yg9.fsf@matt.w80.math-hat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | André Næss | 2000-07-27 14:43:57 | Conditional rule? |
Previous Message | Jerome Raupach | 2000-07-27 13:48:12 | Compile |