SUM() of a SUM() help please! [modified]

[Heinz_]

Hi, i'm having a bit of trouble with this query: "select SUM(t1.value1 - SUM(t2.value2)) from table1 as t1, table2 as t2 where t1.prop=0 and t1.payed=0 and t1.date='2006-3-21' and t2.parent=t1.id"; I can't retrieve the SUM(t1.value1 - SUM(t2.value2)) because i don't know where to insert the group by and what should i group for. I was able to retrieve (t1.value1 - SUM(t2.value2)) if i group by t1.id (wich is the column index) but adding the SUM it doesn't work. Ignore the conditional values, i'm having troubles with the SUM(t1.value1 - SUM(t2.value2)) part. Thanx if someone can helpe me with this one. -- modified at 16:57 Thursday 13th July, 2006

[Ennis Ray Lynch Jr]

Start small SELECT SUM(t1.Value1), SUM(t2.Value2) FROM table1 t1, table2 t2 GROUP BY t1.Value1, t2.Value2 I don't know if the above query works but it is a smaller stepping point. After the above works gradually move towards your more complicated query. I tried in Oracle and it seems to be an expesive query. Maybe breaking it up using T-SQL would help. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane

[Heinz_]

Your query surely works but asume i modified it a step foward as you suggested and i get the following: SELECT (t1.Value1 - SUM(t2.Value2)) FROM table1 t1, table2 t2 GROUP BY t1.id That returns a list of integers that is correct. But now i want the sum of that list. Thanks anyway

[Ennis Ray Lynch Jr]

If you can't get it with one select (with a lot of tweaking you usually can) you can always sutff the results into a table variable and do another select. DECLARE @table TABLE (int a) INSERT INTO @table SELECT (t1.Value1 - SUM(t2.Value2)) FROM table1 t1, table2 t2 GROUP BY t1.id SELECT SUM(a) FROM @table A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane

[Mike Dimmick]

I'd use a subquery:

SELECT SUM(t1.value1 - t2sum)
FROM table1 t1
INNER JOIN
( SELECT parent, SUM( value2 ) as t2sum
FROM t2
GROUP BY parent ) t2
ON
t2.parent = t1.id
WHERE
t1.prop = 0 AND
t1.payed = 0 AND
t1.date = '20060321'

Stability. What an interesting concept. -- Chris Maunder

[Heinz_]

Thanks man! it worked just fine! your're bright man. The only type error you had was "From table2" instead of "from t2". It worked at first run. I'll study the structure of this query to lear how it works. Thanks man again, good luck.