SQL sum with condition

I currently have a large SQ开发者_运维问答L 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?

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.

Try moving 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 might also consider using '0' instead of NULL, as you are doing a sum. It works correctly both ways, but is maybe more indicitive of what your intentions are.

With condition HAVING you will eliminate data with cash not ultrapass 0 if you want, generating more efficiency in your query.

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;