SQL - 使用子查询的正负数总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20543397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Sum of positive and negative numbers using subquery
提问by user3095267
I have a table which contains positive and negative numbers. I have to find out sum of positive and negative numbers using sub query
我有一个包含正数和负数的表格。我必须使用子查询找出正数和负数的总和
回答by No'am Newman
Your question isn't very clear (no table nor column names given), nor is it clear why you need a subquery (never a good idea if it can be avoided). You can get the values that you want by use of the 'case' statement
您的问题不是很清楚（没有给出表名或列名），也不清楚为什么需要子查询（如果可以避免，这绝不是一个好主意）。您可以使用“case”语句获取所需的值
The following countsthe number of positive and negative values
下面统计正负值的个数
select sum (case when acolumn >= 0 then 1 else 0 end) as positive,
sum (case when acolumn < 0 then 1 else 0 end) as negative
from table
whereas the following sumsthe number of positive and negative values
而以下总结了正值和负值的数量
select sum (case when acolumn >= 0 then acolumn else 0 end) as positive,
sum (case when acolumn < 0 then acolumn else 0 end) as negative
from table
回答by kgautron
For the sum of the negative :
对于负数的总和：
SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn < 0
For the sum of the positive:
对于正数的总和：
SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn >= 0
To combine the two (with QUERY1 and QUERY2 being the two previous queries):
将两者结合起来（QUERY1 和 QUERY2 是前两个查询）：
SELECT (QUERY1), (QUERY2)
回答by Vinay
select sum(case when a>=0 then a else 0 end) as positive,
sum(case when a<0 then a else 0 end) as negative
from a
回答by Kalyani Reddy
By using CTE(Common table Expression) we can get the output.
通过使用 CTE(Common table Expression)，我们可以获得输出。
;WITH Psum_CTE
AS
( SELECT SUM(num) AS PositiveSum
FROM sample
WHERE num>=0
)
,Nsum_CTE
AS
(
SELECT SUM(num) AS NegativeSum
FROM sample
WHERE num<0
)
SELECT PositiveSum,NegativeSum
FROM Psum_CTE,Nsum_CTE
回答by Ankit saxena
SELECT (
(SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn < 0 ) -
(SELECT SUM(numberColumn) FROM tableFoo WHERE numberColumn > 0)
) AS totalCalculation
回答by HABO
You can use signto separate the values:
您可以使用sign分隔值：
select Sum( ( Sign( n ) + 1 ) / 2 * n ) as PositiveSum,
Sum( -( Sign( n ) - 1 ) / 2 * n ) as NegativeSum
from YourTableOData;
Signreturns 1, 0or -1depending on the sign of the input value. A little arithmetic can convert that into 1or 0depending on the sign: ( Sign( n ) + 1 ) / 2is 1for all positive values, otherwise 0. Note that the check for negative values (( Sign( n ) - 1 ) / 2) returns -1or 0, hence the negation (-) to avoid flipping the sign of the value that is being summed.
Sign返回1，0或-1取决于输入值的符号。一点算术可以将其转换为1或0取决于符号：( Sign( n ) + 1 ) / 2是1所有正值，否则0。请注意，对负值 ( ( Sign( n ) - 1 ) / 2)的检查返回-1or 0，因此否定 ( -) 以避免翻转正在求和的值的符号。
