How can I sum a group of sums? SQL-Sever 2008
- by billynomates
I have a query with a sum in it like this:
SELECT
Table1.ID, SUM(Table2.[Number1] + Table2.[Number2]) AS SumColumn
FROM Table1 INNER JOIN
Table3 ON Table1.ID = Table3.ID
INNER JOIN
Table2 ON Table3.ID = Table2.ID
WHERE (Table2.[Something] = 'Whatever')
GROUP BY Table1.ID, Table2.[Number1] , Table2.[Number2]
and it gives me a table like this:
ID SumColumn
67 1
67 4
70 2
70 6
70 3
70 6
80 5
97 1
97 3
How can I make it give me a table like this, where the SumColumn is summed, grouped by the ID column?
ID SumColumn
67 5
70 17
80 5
97 4
I cannot GROUP BY SumColumn because I get an error (Invalid column name 'SumColumn'.) COALESCE doesn't work either. Thanks in advance.
EDIT:
Just grouping by the ID gives me an error:
[Number1, Number2 and the other column names that I'm selecting] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
