[SQL] Aggregates and Primary Keys

[Itai Zukerman]

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