Max Case problem

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.

SELECT
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!!!!!!!  

Thanks,
Bob
vmrfadminAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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')

0
 
Scott PletcherSenior DBACommented:
SELECT SUM([Expense Post Amount])
From dbo.mydatabase
Where ([Post Date] Between '04/01/2005' and '04/30/2005')
AND Account IN ('1', '2')
0
 
rafranciscoCommented:
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')
0
 
vmrfadminAuthor Commented:
Wow....you guys are good!  I wish I was that quick on my feet.  It works like a charm!  

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.