• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5959
  • Last Modified:

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!!
0
kcmoore
Asked:
kcmoore
1 Solution
 
matrix_aashCommented:
Select State,MonthVolume,Sum(invoice) from tablename group by monthvolume

If there are same states than you have to consider state also in the group by

Hope this helps

Aash.
0
 
jindalankushCommented:
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
0
 
SQL_SERVER_DBACommented:
what are the field names...

=IIF(Fields!State.Value="DM", SUM(Fields!Invoice.Value), 0)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now