如何SUM同一行中的多个列

时间:2021-03-09 13:17:41

I have the following query below. How do I get the SUM of 'Column 1' and 'Column 2'? X and Y are only sample data to make the question more understandable.

我在下面有以下查询。如何获得“第1列”和“第2列”的总和? X和Y只是样本数据,使问题更容易理解。

SELECT 
'Column 1' = 
    case
        when x = 0 then 2
        when x = 1 then 4
        else 6
    end
,'Column 2' = 
    case
        when y = 0 then 3
        when y = 1 then 6
        else 9
    end
FROM table1

4 个解决方案

#1


1  

i think you can do some think like this using derive table

我认为你可以使用derive表做一些这样的思考

select sum(p.Column 1) as COL1,sum(p.Column 1) as COL2 from 
(
SELECT 
'Column 1' = 
    case
        when x = 0 then 2
        when x = 1 then 4
        else 6
    end
,'Column 2' = 
    case
        when y = 0 then 3
        when y = 1 then 6
        else 9
    end
FROM table1) as p

or using CTE

或使用CTE

;with cte
as
(
SELECT 
    'Column 1' = 
        case
            when x = 0 then 2
            when x = 1 then 4
            else 6
        end
    ,'Column 2' = 
        case
            when y = 0 then 3
            when y = 1 then 6
            else 9
        end
    FROM table1)
select sum(column1) as COL1,sum(column2) as COL2 from cte

#2


0  

Try this query

试试这个查询

SELECT 
sum(
    case
        when x = 0 then 2
        when x = 1 then 4
        else 6
    end) 'Column 1'
,sum( 
    case
        when y = 0 then 3
        when y = 1 then 6
        else 9
    end) 'Column 2'
FROM table1

#3


0  

If you want the result of Column 1 + Column 2 for each row in table1 you can use this query:

如果您希望table1中每行的第1列+第2列的结果,您可以使用此查询:

SELECT [Column 1] + [Column 2]
FROM (
    SELECT  
        CASE
            WHEN x = 0 then 2
            WHEN x = 1 then 4
            ELSE 6
        END AS [Column 1]
        , CASE
            WHEN y = 0 THEN 3
            WHEN y = 1 THEN 6
            ELSE 9
        END AS [Column 2]
    FROM table1
) AS IntermediateResult

If you want the sum of all the values in Column 1 and all the values in Column 2 you can SUM Column 1 and Column 2.

如果您想要第1列中所有值的总和以及第2列中的所有值,则可以使用第1列和第2列。

SELECT SUM([Column 1] + [Column 2])
FROM (
    SELECT  
        CASE
            WHEN x = 0 then 2
            WHEN x = 1 then 4
            ELSE 6
        END AS [Column 1]
        , CASE
            WHEN y = 0 THEN 3
            WHEN y = 1 THEN 6
            ELSE 9
        END AS [Column 2]
    FROM table1
) AS IntermediateResult

#4


0  

Try:

SELECT 
    SUM
    (
        CASE x
            WHEN 0 THEN 2
            WHEN 1 THEN 4
            ELSE 6
        END
    ) AS [Column 1],

    SUM
    (
        CASE y
            WHEN 0 THEN 3
            WHEN 1 THEN 6
            ELSE 9
        END
    ) AS [Column 2]
FROM
    table1

SUM two columns

SUM两列

SELECT 
    (
        CASE x
            WHEN 0 THEN 2
            WHEN 1 THEN 4
            ELSE 6
        END
    )
    +
    (
        CASE y
            WHEN 0 THEN 3
            WHEN 1 THEN 6
            ELSE 9
        END
    ) AS [Column 1]
FROM
    table1

#1


1  

i think you can do some think like this using derive table

我认为你可以使用derive表做一些这样的思考

select sum(p.Column 1) as COL1,sum(p.Column 1) as COL2 from 
(
SELECT 
'Column 1' = 
    case
        when x = 0 then 2
        when x = 1 then 4
        else 6
    end
,'Column 2' = 
    case
        when y = 0 then 3
        when y = 1 then 6
        else 9
    end
FROM table1) as p

or using CTE

或使用CTE

;with cte
as
(
SELECT 
    'Column 1' = 
        case
            when x = 0 then 2
            when x = 1 then 4
            else 6
        end
    ,'Column 2' = 
        case
            when y = 0 then 3
            when y = 1 then 6
            else 9
        end
    FROM table1)
select sum(column1) as COL1,sum(column2) as COL2 from cte

#2


0  

Try this query

试试这个查询

SELECT 
sum(
    case
        when x = 0 then 2
        when x = 1 then 4
        else 6
    end) 'Column 1'
,sum( 
    case
        when y = 0 then 3
        when y = 1 then 6
        else 9
    end) 'Column 2'
FROM table1

#3


0  

If you want the result of Column 1 + Column 2 for each row in table1 you can use this query:

如果您希望table1中每行的第1列+第2列的结果,您可以使用此查询:

SELECT [Column 1] + [Column 2]
FROM (
    SELECT  
        CASE
            WHEN x = 0 then 2
            WHEN x = 1 then 4
            ELSE 6
        END AS [Column 1]
        , CASE
            WHEN y = 0 THEN 3
            WHEN y = 1 THEN 6
            ELSE 9
        END AS [Column 2]
    FROM table1
) AS IntermediateResult

If you want the sum of all the values in Column 1 and all the values in Column 2 you can SUM Column 1 and Column 2.

如果您想要第1列中所有值的总和以及第2列中的所有值,则可以使用第1列和第2列。

SELECT SUM([Column 1] + [Column 2])
FROM (
    SELECT  
        CASE
            WHEN x = 0 then 2
            WHEN x = 1 then 4
            ELSE 6
        END AS [Column 1]
        , CASE
            WHEN y = 0 THEN 3
            WHEN y = 1 THEN 6
            ELSE 9
        END AS [Column 2]
    FROM table1
) AS IntermediateResult

#4


0  

Try:

SELECT 
    SUM
    (
        CASE x
            WHEN 0 THEN 2
            WHEN 1 THEN 4
            ELSE 6
        END
    ) AS [Column 1],

    SUM
    (
        CASE y
            WHEN 0 THEN 3
            WHEN 1 THEN 6
            ELSE 9
        END
    ) AS [Column 2]
FROM
    table1

SUM two columns

SUM两列

SELECT 
    (
        CASE x
            WHEN 0 THEN 2
            WHEN 1 THEN 4
            ELSE 6
        END
    )
    +
    (
        CASE y
            WHEN 0 THEN 3
            WHEN 1 THEN 6
            ELSE 9
        END
    ) AS [Column 1]
FROM
    table1