How can I sum a group of sums? SQL Server 2008

问题:

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.

EDIT 2

No idea why but just grouping by Table.ID now seems to work. Thanks to all who posted the correct answer, I wish I could tick them all!

回答1:

Have 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 see why the above wouldn't work, unless you are not aliasing tables appropriately in the query, which is more of a syntax than logic mistake, but 'wrong' from the SQL engine's perspective regardless. Whenever SQL code doesn't work for me, I simplify ad nauseam my query until the reason for it failing becomes apparent. In that vein, I'd 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'd allow any errors that show up from the following (and nested query!) to inform my further investigation.

回答2:

It sounds like you need to group by just Table1.ID instead.

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 aggregate function, it will handle grouping the numbers together, you just need to specify how to group the other columns 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

回答3:

Try 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

回答4:

Have you tried a nested select?

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