SQL总和与条件(SQL sum with condition)

SQL总和与条件(SQL sum with condition)

我目前有一个大的SQL语句，我添加以下行，以获得每个事务ID的总现金（这是唯一的）：

select sum(cash) from Table a where a.branch = p.branch and a.transID = p.transID) TotalCash

我现在需要做同样的事情，但只能总计在上个月有价值的现金价值，所以我有这样的事情：

select sum(CASE ValueDate WHEN > @startMonthDate THEN cash ELSE NULL END) from Table a where a.branch = p.branch and a.transID = p.transID) TotalMonthCash

对不起，我没有整个声明，但它是存储过程的上下文真的很长和具体，但希望有人会知道我的意思？

I currently have a large SQL statement which i add the following line to in order to get the total cash for each transaction ID (which are unique):

select sum(cash) from Table a where a.branch = p.branch and a.transID = p.transID) TotalCash

and i now need to do the same but only total the cash values that have a valuedate within the last month, so i have something like this:

select sum(CASE ValueDate WHEN > @startMonthDate THEN cash ELSE NULL END) from Table a where a.branch = p.branch and a.transID = p.transID) TotalMonthCash

Sorry that I dont have the whole statement, but it is really long and specific to the context of the stored procedure but was hoping someone would know what i mean?

最满意答案

改为：

SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)

说明

您的CASE表达式语法不正确。 看来，您将简单的CASE表达式语法与搜索到的CASE表达式语法混淆。 请参阅CASE文档 ：

CASE表达式有两种格式：

简单的CASE表达式将表达式与一组简单表达式进行比较，以确定结果。 搜索的CASE表达式计算一组布尔表达式以确定结果。

您想要搜索到的CASE表达式语法：

CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

---

作为附注，如果性能是一个问题，您可能会发现，如果使用JOIN和GROUP BY重写，而不是使用依赖子查询，则此表达式运行速度更快。

Try this instead:

SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)

Explanation

Your CASE expression has incorrect syntax. It seems you are confusing the simple CASE expression syntax with the searched CASE expression syntax. See the documentation for CASE:

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result. The searched CASE expression evaluates a set of Boolean expressions to determine the result.

You want the searched CASE expression syntax:

CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

---

As a side note, if performance is an issue you may find that this expression runs more quickly if you rewrite using a JOIN and GROUP BY instead of using a dependent subquery.