Need Median to follow Group By Case

Question

I was wondering if I could please get some help in making the median calculation run for each DonorType group. With the query I've got it just does a median for all values, not the values from each case grouping. I've tried a few things and looked around but I'm stuck.

Here's what I've got:

SELECT DonorType = CASE WHEN [Criteria1] THEN 'AutoRecurring'
                    WHEN [Criteria2] THEN 'ManualRecurring'
                    ELSE 'NonRecurring' END,
COUNT(DISTINCT AccountNumber) AS TotalDonors, MIN(TotalDonationAmount) AS MinAmount,
MAX(TotalDonationAmount) AS MaxAmount, AVG(TotalDonationAmount) AS AvgAmount,
((SELECT MAX(TotalDonationAmount) 
    FROM (SELECT TOP 50 PERCENT TotalDonationAmount 
        FROM #TempDonors
     ORDER BY TotalDonationAmount) AS BottomHalf)
+ (SELECT MIN(TotalDonationAmount) 
    FROM (SELECT TOP 50 PERCENT TotalDonationAmount 
        FROM #TempDonors
        ORDER BY TotalDonationAmount DESC) AS TopHalf)) / 2 AS MedAmount
FROM #TempDonors
WHERE TotalDonationAmount > 0
GROUP BY CASE WHEN [Criteria1] THEN 'AutoRecurring'
                WHEN [Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END

Thank you so much in advance for your help. If there's another post asking this (I'm sure there is, I just don't know what quite to search for), by all means just post the link - I apologize in advance for my inability to find it.

Take care!

Answer 1

You need to correlate the median calculation to the outer query.

It would look something like this:

...
((SELECT MAX(TotalDonationAmount) 
    FROM (SELECT TOP 50 PERCENT TotalDonationAmount 
        FROM #TempDonors t1
        WHERE CASE WHEN t.[Criteria1] THEN 'AutoRecurring'
                WHEN t.[Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END=CASE WHEN t1.[Criteria1] THEN 'AutoRecurring'
                WHEN t1.[Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END
     ORDER BY TotalDonationAmount) AS BottomHalf)
+ (SELECT MIN(TotalDonationAmount) 
    FROM (SELECT TOP 50 PERCENT TotalDonationAmount 
        FROM #TempDonors t2
        WHERE CASE WHEN t.[Criteria1] THEN 'AutoRecurring'
                WHEN t.[Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END=CASE WHEN t2.[Criteria1] THEN 'AutoRecurring'
                WHEN t2.[Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END
        ORDER BY TotalDonationAmount DESC) AS TopHalf)) / 2 AS MedAmount
FROM #TempDonors
WHERE TotalDonationAmount > 0
GROUP BY CASE WHEN [Criteria1] THEN 'AutoRecurring'
                WHEN [Criteria2] THEN 'ManualRecurring'
                ELSE 'NonRecurring' END