[newbie] SQL-query with SUM() doesnt work. :(

Hi NG,

I have two tables:

TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

And this is the query, that I'm trying to run on this tables:

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

Well - it doesn't work at all! :(
That is the reason?!

Appreciate everyone's help!!!

S.B.well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

Example:

select t1.id1, t2.text, sum(t1.value)
from table1 t1, table2 t2
where t1.id1 = t2.id1
group by t1.id1, t2.text
order by t1.id1 asc|||Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||Thanks, mkkmg and alligatorsql.com !!! :)

"group by TABLE1.id1, TABLE2.text" works perfectly!

S.B.

Originally posted by alligatorsql.com
Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com