Sum based on condition

Posted on 2007-10-18
Last Modified: 2008-01-09
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.
Question by:kcmoore
    LVL 8

    Expert Comment

    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

    LVL 4

    Expert Comment

    select MonthVolume ,totals = sum( Invoice)
    from table_name group by MonthVolume
    where  DM State = DM


    select MonthVolume,TOTAL = sum(CASE WHEN  DM State = DM THEN  Invoice ELSE 0 END),
    from table_name group by MonthVolume
    where  DM State = DM
    LVL 16

    Accepted Solution

    what are the field names...

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

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now