Aggregate function on an expression containing an aggregate or a subquery

Question

I'm getting an error

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

which is this line:

,MIN(CASE WHEN DateCompleted IS NULL THEN MIN(DateReceived) ELSE '' END) AS Oldest_Claim

I'm not sure how to handle this because I can't include DateCompleted in the GROUP BY clause and get the results that I need. Thanks

DECLARE @StartDate smalldatetime = '1/1/2000'
DECLARE @EndDate smalldatetime = '3/13/2019'


SELECT 
   CASE 
        WHEN DischargeType  = 'atb' THEN 'Ability to Benefit' 
        WHEN DischargeType  = 'cls' THEN 'Closed School'
        WHEN DischargeType  = 'death' THEN 'Death'
        WHEN DischargeType  = 'dqs' THEN 'Disqualifying Status'
        WHEN DischargeType  = 'fraud' THEN 'Fraud'
        WHEN DischargeType  = 'id theft' THEN 'ID Theft'
        WHEN DischargeType  = 'ineligible borrower' THEN 'Ineligible Borrower'
        WHEN DischargeType  = 'tlf' THEN 'Teacher Loan Forgiveness'
        WHEN DischargeType  = 'uns' THEN 'Unauthorized Signature/Payment'
        WHEN DischargeType  = 'unp' THEN 'Unpaid Refund'
   END AS DischargeType
   ,UPPER(Servicer) AS Servicer    
   ,'' AS Outstanding
   --Intentionally using DateLoaded here instead of DateReceived
   ,SUM(CASE WHEN (DateLoaded > @StartDate AND DateLoaded < DATEADD(dd, 1, @EndDate)) THEN 1 ELSE 0 END) AS Claims_Loaded
   ,SUM(CASE WHEN DateCompleted IS NOT NULL AND Approve = 1 AND DateCompleted > @StartDate AND DateCompleted < DATEADD(dd, 1, @EndDate) THEN 1 ELSE 0 END) AS Claims_Approved
   ,SUM(CASE WHEN DateCompleted IS NOT NULL AND Approve = 0 AND DateCompleted > @StartDate AND DateCompleted < DATEADD(dd, 1, @EndDate) THEN 1 ELSE 0 END) AS Claims_Denied
   ,SUM(CASE WHEN DateCompleted IS NULL THEN 1 ELSE 0 END) AS Claims_Pending
   ,MIN(CASE WHEN DateCompleted IS NULL THEN MIN(DateReceived) ELSE '' END) AS Oldest_Claim
   ,ROUND((SUM(CASE WHEN DateCompleted IS NOT NULL AND Approve = 0 AND DateCompleted > @StartDate AND DateCompleted < DATEADD(dd, 1, @EndDate) THEN 1 ELSE 0 END) / 
   NULLIF(CAST(SUM(CASE WHEN (DateCompleted > @StartDate AND DateCompleted < DATEADD(dd, 1, @EndDate)) THEN 1 ELSE 0 END)AS Float),0) *100),2) AS Percent_Denied 
FROM 
    Claims 
WHERE 
    DischargeType IN ('atb','cls','death','dqs','fraud','id theft','ineligible borrower','tlf','uns','unp')
GROUP BY 
    DischargeType, Servicer
ORDER BY DischargeType, Servicer

The Impaler The Impaler · Accepted Answer · 2019-03-15T19:01:48

You must change the expression:

MIN(CASE WHEN DateCompleted IS NULL THEN MIN(DateReceived) ELSE '' END)

To something like:

MIN(CASE WHEN DateCompleted IS NULL THEN (
  select MIN(DateReceived) from ...
) ELSE '' END)

Probably you'll be better off including that subquery in a CTE.

Aggregate function on an expression containing an aggregate or a subquery

1 Answers