'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 and lengthcell=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 > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
