Max Case problem

Posted on 2005-04-19
Last Modified: 2010-08-05
Hey all,

I have a question regarding the use of Max Case.....I am trying to return some data from a database using the max case syntax, but I am struggling a little.  Here is my example below.

Max (Case WHEN
      ((Account = '1') or(Account = '2'))
      Then Sum[Expense Post Amount] Else 0 END)
      as [Total Expenses]

From dbo.mydatabase
Where ([Post Date] Between '04/01/2005' and '04/30/2005')

Sample Dataset

Account            Date             Expense Post Amount
1                     04/02/2005    1.00
1                     04/15/2005    2.00
1                     04/16/2005    3.00
2                     04/02/2005    1.00

I am looking to return 7.00 as [Total Expense]

I am screwing up the sum part of the code.  It seems to work ok until I start trying to sum up the data that is either account 1 and or account 2.

Any suggestions or ideas would be really appreciated!!!!!!!  

Question by:vmrfadmin
    LVL 68

    Expert Comment

    SELECT SUM([Expense Post Amount])
    From dbo.mydatabase
    Where ([Post Date] Between '04/01/2005' and '04/30/2005')
    AND Account IN ('1', '2')
    LVL 68

    Accepted Solution

    But if you have the situation where you want a total of *all* accounts and a separate total for '1' and '2' accounts, then you can do this:

    SELECT SUM([Expense Post Amount]) AS [Total Expenses],
        SUM(CASE WHEN Account IN ('1', '2') THEN [Expense Post Amount] ELSE 0 END) AS [Acct 1&2 Expenses]
    From dbo.mydatabase
    Where ([Post Date] Between '04/01/2005' and '04/30/2005')

    LVL 28

    Expert Comment

    Try this:

    SELECT SUM(CASE WHEN Account IN ('1', '2') THEN [Expense Post Amount] ELSE 0 END) AS  [Total Expenses]
    From dbo.mydatabase
    Where ([Post Date] Between '04/01/2005' and '04/30/2005')

    Author Comment

    by:vmrfadmin guys are good!  I wish I was that quick on my feet.  It works like a charm!  

    Thanks Scott and rafranciso............


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    755 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

    26 Experts available now in Live!

    Get 1:1 Help Now