Aggregates and Primary Keys

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

Responses

Browse pgsql-sql by date

  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