我怎样才能总结一组总和?SQL Server 2008 [英] How can I sum a group of sums? SQL Server 2008

问题描述

我有一个带有 sum 的查询，如下所示:

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

我怎样才能让它给我一个像这样的表格，其中 SumColumn 被求和，按 ID 列分组?

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

我不能 GROUP BY SumColumn 因为我得到一个错误(无效的列名'SumColumn'.)COALESCE 也不起作用.提前致谢.

I cannot GROUP BY SumColumn because I get an error (Invalid column name 'SumColumn'.) COALESCE doesn't work either. Thanks in advance.

仅按 ID 分组会给我一个错误:

[Number1、Number2 和我选择的其他列名] 在选择列表中无效，因为它既不包含在聚合函数或 GROUP BY 子句中.

编辑 2

不知道为什么，但现在似乎可以按 Table.ID 分组.感谢所有发布正确答案的人，我希望我能全部打勾！

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!

推荐答案

你试过了吗:

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

我不明白为什么上述方法行不通，除非您没有在查询中适当地对表进行别名，这更像是一种语法而不是逻辑错误，但无论如何从 SQL 引擎的角度来看都是错误的".每当 SQL 代码对我不起作用时，我会简化 令人作呕我的查询，直到它失败的原因变得明显.在这种情况下，我会尝试:

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.

这篇关于我怎样才能总结一组总和?SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！