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