某些列的 SQL 行总和 [英] SQL Row Sum for some colums

问题描述

我正在尝试开发一个 SQL 字段，该字段是除前两行之外的所有列的总和.下面是我的代码，它返回列 MonthYear、Total、Sum1、Sum2 和 Sum3.我想要一个总和为 Sum1、Sum2 和 Sum3 的 FinalSum.这将用于反检查 Total 列的准确性.有没有办法做到这一点?运行 Microsoft SQL Server 2005 - T-SQL

I am attempting to develop an SQL field that is the sum of all columns for the row excluding the first two. Below is my code, which returns columns MonthYear, Total, Sum1, Sum2, and Sum3. I'd like to have a FinalSum which totals Sum1,Sum2, and Sum3. This is to be used to counter check the Total column for accuracy. Is there a way to do this? Running Microsoft SQL Server 2005 - T-SQL

SELECT MonthYear,
            COUNT(*) AS Total,
            ISNULL(
                SUM(
                    CASE 
                    WHEN Status='Sum1'                  THEN 1
                    ELSE 0
                    END
                )
            ,0) AS [Sum1],
ISNULL(
                SUM(
                    CASE 
                    WHEN Status='Sum2'                  THEN 1
                    ELSE 0
                    END
                )
            ,0) AS [Sum2],
ISNULL(
                SUM(
                    CASE 
                    WHEN Status='Sum3'                  THEN 1
                    ELSE 0
                    END
                )
            ,0) AS [Sum3]
FROM tablename

GROUP BY
    MonthYear

推荐答案

您的查询中缺少 FROM.无论如何，一种方法是使用派生表:

You are missing a FROM on your query. Anyway, one way would be using a derived table:

SELECT *, [Sum1]+[Sum2]+[Sum3] AS TotalSum
FROM (Your Current Select Here) AS T

对于 SQL Server 2005+，您还可以使用 CTE:

For SQL Server 2005+, you can also use CTEs:

;WITH CTE AS 
(
    Your Current Select Here
)
SELECT *, [Sum1]+[Sum2]+[Sum3] AS TotalSum
FROM CTE

这篇关于某些列的 SQL 行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！