How can I sum a group of sums? SQL-Sever 2008

Posted by billynomates on Stack Overflow See other posts from Stack Overflow or by billynomates
Published on 2011-03-08T15:39:34Z Indexed on 2011/03/08 16:10 UTC
Read the original article Hit count: 129

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.

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about query