'group by' must be your problem.
If you remove that clause from your second query, you should then get
the same result (77) sum'ing the temp table...
I hope, it helps...
Dima
Marco Kienzle wrote:
>The following problem occur using the sum() function (see the attached
>file for all the details and an example):
> - if you use it on a portion of a table (example: table age) you get a
>result that differ from the one you can get by hand (see the whole table
>temp1 and do the sum by hand)
> - if you use the sum() function on the complete table (i.e. if you
>first create a temporary table and then run the sum() function) then you
>get the right result (see example on temp1).
>
>Conclusion: I am not sure its really a bug but this differences can be
>really misleading.
>
>Have a nice day,
>marco
>
>
>------------------------------------------------------------------------
>
>SYSTEM INFORMATION
>Distribution: Red Hat Linux
>Operating System: Linux
>Distribution Version: Red Hat Linux release 7.2 (Enigma)
>
>Operating System Version: #1 Thu Sep 6 17:27:27 EDT 2001
>Operating System Release: 2.4.7-10
>Processor Type: i686
>------------------------------------------------------------------------------
>THE VERSION OF POSTGRESQL
>
>bash-2.05$ psql --version
>psql (PostgreSQL) 7.1.3
>contains readline, history, multibyte support
>Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
>Portions Copyright (c) 1996 Regents of the University of California
>Read the file COPYRIGHT or use the command \copyright to see the
>usage and distribution terms.
>
>------------------------------------------------------------------------------
>THE DESCRIPTION OF THE TABLE AGE
>
>herring=# \d age
> Table "age"
> Attribute | Type | Modifier
>---------------+-----------------------+----------
> code | character varying(10) |
> inst | character varying(10) |
> year | smallint |
> quart | smallint |
> month | smallint |
> reg | character varying(10) |
> div | character varying(10) |
> subdiv | character varying(10) |
> gridcell | character varying(10) |
> sp | character varying(50) |
> stock | character varying(10) |
> samptype | character varying(10) |
> lengthcell | smallint |
> sex | character varying(10) |
> age | smallint |
> agenum | bigint |
> weightmeanage | double precision |
>
>----------------------------------------------------------------------------
>THE SUM QUERY ON THE TABLE AGE
>
>herring=# select sum(agenum) from age where weightmeanage>0 and lengthcell=160;
> sum
>-----
> 77
>(1 row)
>
>-----------------------------------------------------------------------------
>CREATION OF THE TABLE TEMP1
>
> select INTO TABLE temp1 inst, year, month, lengthcell, sex, age, agenum, weightmeanage from age where weightmeanage>0
andlengthcell=160 group by inst, year, month, lengthcell, sex,age,agenum, weightmeanage;
>------------------------------------------------------------------------------
>THE TABLE DESCRIPTION OF THE TABLE TEMP1
>
>herring=# \d temp1
> Table "temp1"
> Attribute | Type | Modifier
>---------------+-----------------------+----------
> inst | character varying(10) |
> year | smallint |
> month | smallint |
> lengthcell | smallint |
> sex | character varying(10) |
> age | smallint |
> agenum | bigint |
> weightmeanage | double precision |
>
>----------------------------------------------------------------------
>THE WHOLE TABLE TEMP1
>
>herring=# select * from temp1;
> inst | year | month | lengthcell | sex | age | agenum | weightmeanage
>------+------+-------+------------+-----+-----+--------+---------------
> IMR | 1991 | 2 | 160 | F | 1 | 10 | 31
> IMR | 1991 | 2 | 160 | M | 1 | 3 | 32
> IMR | 1992 | 2 | 160 | F | 1 | 1 | 25
> IMR | 1992 | 2 | 160 | F | 1 | 1 | 26
> IMR | 1992 | 2 | 160 | M | 1 | 1 | 25
> IMR | 1992 | 2 | 160 | M | 1 | 1 | 28
> IMR | 1992 | 5 | 160 | M | 1 | 1 | 34
> IMR | 1992 | 6 | 160 | F | 1 | 1 | 30
> IMR | 1992 | 10 | 160 | F | 0 | 0 | 34
> IMR | 1992 | 10 | 160 | M | 0 | 0 | 25
> IMR | 1993 | 2 | 160 | F | 1 | 1 | 27
> IMR | 1993 | 2 | 160 | F | 1 | 1 | 28
> IMR | 1993 | 2 | 160 | F | 1 | 1 | 29
> IMR | 1993 | 2 | 160 | F | 1 | 1 | 30
> IMR | 1993 | 2 | 160 | M | 1 | 1 | 27
> IMR | 1993 | 2 | 160 | M | 1 | 1 | 28
> IMR | 1993 | 2 | 160 | M | 1 | 1 | 29
> IMR | 1993 | 11 | 160 | F | 0 | 0 | 30
> IMR | 1993 | 11 | 160 | M | 0 | 0 | 28
> IMR | 1994 | 5 | 160 | F | 1 | 1 | 38
> IMR | 1994 | 5 | 160 | F | 1 | 1 | 42
> IMR | 1994 | 5 | 160 | M | 1 | 1 | 34
> IMR | 1994 | 5 | 160 | M | 1 | 1 | 39
> IMR | 1994 | 11 | 160 | F | 0 | 0 | 31
> IMR | 1995 | 1 | 160 | F | 1 | 1 | 27
> IMR | 1995 | 1 | 160 | F | 1 | 1 | 28
> IMR | 1995 | 1 | 160 | F | 2 | 2 | 28
> IMR | 1995 | 1 | 160 | F | 2 | 2 | 29
> IMR | 1995 | 1 | 160 | M | 1 | 1 | 26
> IMR | 1995 | 1 | 160 | M | 1 | 1 | 27
> IMR | 1995 | 1 | 160 | M | 1 | 1 | 29
> IMR | 1995 | 1 | 160 | M | 1 | 1 | 30
> IMR | 1995 | 1 | 160 | M | 2 | 2 | 26
> IMR | 1995 | 5 | 160 | F | 1 | 1 | 29
> IMR | 1995 | 5 | 160 | F | 1 | 1 | 34
> IMR | 1995 | 5 | 160 | F | 1 | 1 | 37
> IMR | 1995 | 5 | 160 | F | 1 | 1 | 38
> IMR | 1995 | 5 | 160 | M | 1 | 1 | 23
> IMR | 1995 | 5 | 160 | M | 1 | 1 | 37
> IMR | 1995 | 6 | 160 | F | 1 | 1 | 32
> IMR | 1995 | 6 | 160 | M | 1 | 1 | 32
> IMR | 1995 | 7 | 160 | F | 1 | 1 | 31
> IMR | 1995 | 7 | 160 | F | 1 | 1 | 37
> IMR | 1995 | 7 | 160 | M | 1 | 1 | 32
> IMR | 1995 | 7 | 160 | M | 1 | 1 | 35
> IMR | 1995 | 11 | 160 | F | 0 | 0 | 29
> IMR | 1995 | 11 | 160 | F | 0 | 0 | 31
> IMR | 1995 | 11 | 160 | M | 0 | 0 | 29
> IMR | 1995 | 11 | 160 | M | 0 | 0 | 31
> IMR | 1995 | 11 | 160 | M | 0 | 0 | 33
> IMR | 1996 | 2 | 160 | F | 1 | 1 | 29
> IMR | 1996 | 2 | 160 | F | 1 | 1 | 31
> IMR | 1996 | 2 | 160 | M | 1 | 1 | 28
> IMR | 1996 | 11 | 160 | M | 0 | 0 | 32
> IMR | 1997 | 5 | 160 | F | 1 | 1 | 41
> IMR | 1997 | 5 | 160 | M | 1 | 1 | 39
> IMR | 1997 | 5 | 160 | M | 1 | 1 | 46
> IMR | 1997 | 7 | 160 | F | 1 | 1 | 30
> IMR | 1997 | 7 | 160 | M | 1 | 1 | 28
> IMR | 1997 | 7 | 160 | M | 1 | 1 | 32
> IMR | 1998 | 6 | 160 | F | 1 | 1 | 40
> IMR | 1998 | 6 | 160 | M | 1 | 1 | 31
> IMR | 1998 | 11 | 160 | F | 0 | 0 | 29
> IMR | 1998 | 11 | 160 | M | 0 | 0 | 29
> IMR | 1999 | 2 | 160 | F | 1 | 1 | 26
> IMR | 1999 | 6 | 160 | F | 1 | 1 | 29
> IMR | 1999 | 7 | 160 | F | 1 | 1 | 32
> IMR | 1999 | 7 | 160 | F | 1 | 1 | 33
> IMR | 1999 | 7 | 160 | F | 1 | 1 | 34
> IMR | 1999 | 7 | 160 | M | 1 | 1 | 30
> IMR | 1999 | 7 | 160 | M | 1 | 1 | 31
> IMR | 1999 | 7 | 160 | M | 1 | 1 | 32
>(72 rows)
>------------------------------------------------------------------------------
>THE SUM QUERY ON TEMP1
>
>herring=# select sum(agenum) from temp1;
> sum
>-----
> 73
>(1 row)
>
>______________________________________________________________________________
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
