kcmoore
asked on
Sum based on condition
I am using SQL Reporting Services 2005 and have a column on my report that I need to sum based on a condition. Example: If [DM State] = DM then Sum [Invoice].
My data set contains DM State, Invoice, MonthVolume. I have a group on MonthVolume, and have a secondary header containing my totals, which is where I need to place this conditional sum.
Thanks!!
My data set contains DM State, Invoice, MonthVolume. I have a group on MonthVolume, and have a secondary header containing my totals, which is where I need to place this conditional sum.
Thanks!!
select MonthVolume ,totals = sum( Invoice)
from table_name group by MonthVolume
where DM State = DM
OR
select MonthVolume,TOTAL = sum(CASE WHEN DM State = DM THEN Invoice ELSE 0 END),
from table_name group by MonthVolume
where DM State = DM
from table_name group by MonthVolume
where DM State = DM
OR
select MonthVolume,TOTAL = sum(CASE WHEN DM State = DM THEN Invoice ELSE 0 END),
from table_name group by MonthVolume
where DM State = DM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If there are same states than you have to consider state also in the group by
Hope this helps
Aash.