SQL sum with condition

Question

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

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

and now I need to do the same thing, but only the total number of monetary values ​​that have been significant in 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 I don’t have the whole statement, but it is really long and context specific to the stored procedure, but was hoping someone would find out what I mean?

Answer 1

Try this instead:

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

Explanation

Your CASE expression has the wrong syntax. It seems you are mixing the simple syntax of a CASE expression with the syntax of a CASE expression. See the documentation for CASE :

The CASE expression has two formats:

• Simple expression CASE compares an expression with a set of simple expressions to determine the result.
• The selected CASE expression evaluates a set of Boolean expressions to determine the result.

You want the syntax of the CASE expression you are looking for:

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

---

As a side note, if performance is a problem, you may find that this expression is faster if you rewrite the use of JOIN and GROUP BY instead of using the dependent subquery.

Answer 2

Try moving the ValueDate :

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

(reformatted for clarity)

You can also use '0' instead of NULL, since you are making a sum. It works correctly in both directions, but perhaps more indicative of what your intentions are.

Answer 3

With the HAVING clause, you delete data with cash not exceeding 0 if you wish, which will increase the efficiency of your request.

 SELECT SUM(cash) AS money FROM Table t1, Table2 t2 WHERE t1.branch = t2.branch AND t1.transID = t2.transID AND ValueDate > @startMonthDate HAVING money > 0;