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

问题描述

我有一个包含 sum 的查询，如下所示：

  SELECT 
 Table1.ID，SUM（Table 2. [Number1] + Table2。[Number2]）AS SumColumn 
 FROM Table1 INNER JOIN 
 Table3 ON Table1.ID = Table3.ID 
 INNER JOIN 
 Table 2 ON Table3.ID = Table2.ID 
 WHERE（Table 2. [Something] ='Whatever'）
 
 GROUP BY Table1.ID，Table2。[ Number1]，Table2。[Number2] 
  

它给了我一张这样的表格：

  ID SumColumn 
 67 1 
 67 4 
 70 2 
 70 6 
 70 3 
 70 6 
 80 5 
 97 1 
 97 3 
  

我怎样才能让它给我这样的一个表格，SumColumn是按照ID列分组的呢？

  ID SumColumn 
 67 5 
 70 17 
 80 5 
 97 4 
  

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

编辑： b
ID给我一个错误：

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

编辑2

不知道为什么，但现在看来只是通过Table.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 
  

我不明白为什么上面不会工作，除非你没有在查询中恰当地对表进行别名表达，这更多的是语法而不是逻辑错误，但是不管从SQL引擎的角度来看都是错误的。无论何时SQL代码不适用于我，我简化了 ad nauseam 查询，直到其失败的原因变得明显。在这方面，我会尝试：

  SELECT ID，SUM（sumCol）as SumColumn 
 FROM（
 SELECT 
 Table1.ID，（Table 2. [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 
  

...我会允许任何从下面显示的错误（和嵌套查询！）通知我进一步调查。

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!

解决方案

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.

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