asked on

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

Scott Pletcher

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rafrancisco

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')

vmrfadmin

ASKER

Wow....you guys are good! I wish I was that quick on my feet. It works like a charm!

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