To: pgsql-sql@postgresql.org
Subject: Aggregates and Primary Keys
From: Itai Zukerman <zukerman@math-hat.com>
Date: 27 Jul 2000 10:00:54 -0400
Message-ID: <87k8e78yg9.fsf@matt.w80.math-hat.com>
Lines: 50
User-Agent: Gnus/5.0807 (Gnus v5.8.7) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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