| 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: | Whole Thread | Raw Message | 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 |