How to Sum Cash Values in SQL Based on a Monthly Condition?

SQL Sum with Condition (Including Month-Based Calculations)

You have a large SQL statement that calculates the total cash for each unique transaction ID using the following line:

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

Now, you need to modify the statement to only total cash values that have a ValueDate within the last month. Here's a solution:

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

Explanation

The CASE statement uses the searched CASE expression syntax to evaluate a Boolean expression (in this case, WHEN ValueDate > @startMonthDate) and return a corresponding result (here, cash). If the condition is not met, it returns 0 instead.

Optimization Tip

If performance is a concern, consider using a JOIN and GROUP BY instead of a dependent subquery for better efficiency.

The above is the detailed content of How to Sum Cash Values in SQL Based on a Monthly Condition?. For more information, please follow other related articles on the PHP Chinese website!