SQL Row Sum for some colums

问题:

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

回答1:

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

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

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

回答2:

Ok - probably not the most elegant way (copy saved here on SqlFiddle)

CREATE TABLE this_name 
(
    monthYear DateTime,
    status varchar(20), 
   );

INSERT INTO this_name
(monthYear, status)
VALUES
('01 jan 2012', 'Sum1'),
('01 jan 2012', 'Sum1'),
('01 feb 2012', 'Sum2'),
('01 feb 2012', 'Sum1'),
('01 apr 2012', 'Sum3'),
('01 jan 2013', 'Sum3'),
('01 jan 2013', 'Sum3'),
('01 jan 2012', 'Sum3')

;WITH myCTE (MonthYear,Total, Sum1, Sum2, Sum3)
AS
(
SELECT MonthYear,
       COUNT(*) AS Total,
       SUM(CASE WHEN ISNULL(Status,'X')='Sum1' THEN 1 ELSE 0 END) AS [Sum1],
       SUM(CASE WHEN ISNULL(Status,'X')='Sum2'THEN 1 ELSE 0 END ) AS [Sum2],
       SUM(CASE WHEN ISNULL(Status,'X')='Sum3' THEN 1 ELSE 0 END) AS [Sum3] 
FROM this_name
GROUP BY MonthYear
)   
SELECT 
  MonthYear,
  Total, 
  Sum1, 
  Sum2, 
  Sum3,
  (Sum1 + Sum2 + Sum3) [CountTotal]
FROM myCTE

Another alternative is this one ([SQLFiddle];

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

   SUM(CASE WHEN ISNULL(Status,'X')IN('Sum1','Sum2','Sum3') THEN 1 ELSE 0 END) AS [SumExtra]  
FROM this_name
GROUP BY MonthYear

回答3:

declare @sum1 int, @sum2 int, @sum3 int
declare @totalSum int = 0

select @sum1 = ISNULL(SUM(CASE WHEN Status='Sum1' THEN 1 ELSE 0 END), 0)
from tablename

select @sum2 = ISNULL(SUM(CASE WHEN Status='Sum2' THEN 1 ELSE 0 END), 0)
from tablename

select @sum3 = ISNULL(SUM(CASE WHEN Status='Sum3' THEN 1 ELSE 0 END), 0)
from tablename

set @totalSum = @sum1 + @sum2 + @sum3

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],
            @totalSum
FROM tablename

GROUP BY
    MonthYear

回答4:

One way using a Temp table

CREATE TABLE #Table2
(
    MonthYear VARCHAR(6) NOT NULL PRIMARY KEY,
    Total INT NOT NULL,
    Sum1 INT NOT NULL,
    Sum2 INT NOT NULL,
    Sum3 INT NOT NULL
) ;

INSERT INTO #Table2
SELECT
        MonthYear,
        COUNT(*) AS Total,
        SUM(CASE WHEN ISNULL(Status, '') = 'Sum1' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ISNULL(Status, '') = 'Sum2' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ISNULL(Status, '') = 'Sum3' THEN 1 ELSE 0 END)
FROM tablename
GROUP BY MonthYear ;

SELECT *, Sum1Count + Sum2Count + Sum3Count AS FinalSum FROM #Table2 ;

回答5:

Another way with repeating calculation

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