Two aggregate functions one with criteria

[djj55]

Hello, I am trying to convert from an Access macro to a stored procedure in SQL 2000 I have two queries

SELECT 
    myGroup
    , Sum(myAmount) AS SumAMT 
FROM myTable
GROUP BY myGroup

SELECT
    myGroup
    , Max(myDate) AS MaxDATE 
FROM myTable
WHERE mySource IN ('abc', 'def') 
GROUP BY myGroup

I would like to have only one query, however I do not know how to handle the criteria for the date.

Does this just have to be two queries? Can a CASE statement be used?

Thank you,



djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

This should work:

SELECT
    myGroup
    , Sum(myAmount) AS SumAMT
    , Max(Case When mySource in ('abc','def') Then myDate Else NULL End) As MaxDate
FROM myTable
GROUP BY myGroup

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George! I thought the CASE statement would work but did not know what to use for the ELSE. Dah, NULL should have been obvious.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Truth is, the ELSE part wasn't necessary. If the data doesn't satisfy any of the WHEN parts, the result would be null anyway. This works because the MAX aggregate ignores null values.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom