SQL sum with condition

Question

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  gt   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

User · Accepted Answer

Try this instead   SUM CASE WHEN ValueDate  gt   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

User · Answer

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  gt   startMonthDate HAVING money  gt  0

User · Answer

Try moving ValueDate   select sum CASE                WHEN ValueDate  gt   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

[sql] SQL sum with condition

Examples related to sql