How can I sum the sum of the sums? SQL Server 2008

Question

I have a query with sum in it as follows:

 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 he 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 get him to give me a table like this, where the total amount grouped by ID column is summed?

 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 does not work either. Thanks in advance.

EDIT:

Just grouping by id gives me an error:

[Number1, Number2, and other column names that I select] are not allowed in the select list because it is not contained in the aggregate function or in the GROUP BY clause.

EDIT 2

I don't know why, but just grouping by Table.ID now works. Thanks to everyone who posted the correct answer, I would like me to notice them all!

Answer 1

You tried:

 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 

I can’t understand why this didn’t work, if you didn’t write the tables in the query correctly, this is more of a syntax than a logical error, but "wrong" from the point of view of the SQL engine, independently. Whenever the SQL code does not work for me, I simplify the declaration, nauseating my query, until the reason for its failure becomes obvious. In this vein, I would try:

 SELECT ID, SUM(sumCol) as SumColumn FROM ( SELECT Table1.ID, (Table2.[Number1] + Table2.[Number2]) AS sumCol 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 

... and I would have made any errors arising from the following (and the subquery!) to report my further investigation.

Answer 2

It looks like you need to group only using Table1.ID.

 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 

Since SUM () is an aggregated function, it will handle the grouping of numbers together, you just need to specify how to group the remaining columns that you want.

[UPDATE]

Demo code:

 CREATE TABLE #T1( ID INT ) CREATE TABLE #T2 ( ID INT, Something VARCHAR(32), Number1 INT, Number2 INT ) CREATE TABLE #T3 ( ID INT ) DECLARE @Index INT = 0 WHILE @Index < 50 BEGIN DECLARE @Something VARCHAR(32) = '' SET @Index = @Index + 1 IF @Index BETWEEN 0 AND 15 SET @Something = 'Blah' ELSE IF @Index BETWEEN 15 AND 40 SET @Something = 'Whatever' ELSE IF @Index BETWEEN 40 AND 50 SET @Something = 'Bleh' INSERT INTO #T1 VALUES(@Index) INSERT INTO #T3 VALUES(@Index) INSERT INTO #T2 VALUES(@Index, @Something, RAND()*100, RAND() * 100) INSERT INTO #T2 VALUES(@Index, @Something, RAND()*100, RAND() * 100) INSERT INTO #T2 VALUES(@Index, @Something, RAND()*100, RAND() * 100) END SELECT #T1.ID, SUM(#T2.Number1 + #T2.Number2) AS SumColumn FROM #T1 INNER JOIN #T3 ON #T1.ID = #T3.ID INNER JOIN #T2 ON #T3.ID = #T2.ID WHERE (#T2.Something = 'Whatever') GROUP BY #T1.ID 

Answer 3

Try it?

 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 

Answer 4

Have you tried the nested choice?

 SELECT ID, SUM(SumColumn) AS SumColumn FROM (SELECT Table1.ID AS 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]) GROUP BY ID