Aggregate function Sum not working when specifying subquery

Question

This is my query:

SELECT 
    Sum(IsNull(CircuitCourtFeeCap, 0) + IsNull(CircuitCourtCourtTime, 0) + IsNull(CircuitCourtWaiverFee, 0) + IsNull(CircuitCourtExpenseFee, 0)) AS TotalApproved,
    Sum(IsNull(e.FeeAmountCap, 0)) AS TotalFeeCap,
    Sum(IsNull((
                SELECT Claimed
                FROM [dbo].[CourtTimes] ct
                WHERE ct.DocumentNameID = dn.DocumentNameID
                ), 0)) AS TotalCourtTime
FROM DocumentNames dn
LEFT JOIN Expenses e ON dn.DocumentNameID = e.DocumentNameID

It is super simple. All Sum except this piece of line is not working:

Sum(IsNull((Select Claimed From [dbo].[CourtTimes] ct Where ct.DocumentNameID = dn.DocumentNameID), 0)) As TotalCourtTime

I get this error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any ideas how to solve this issue?

You almost have it.Move the SUM after the SELECT and put brackets around it all. Here's a good example: essentialsql.com/… — Nick.Mc
– Nick.Mc, Commented May 3, 2017 at 13:15
@Nick.McDermaid: That will not help. Here is the error I get if I try to do that: Column 'DocumentNames.DocumentNameID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — Tim Liberty
– Tim Liberty, Commented May 3, 2017 at 13:24

JNevill · Accepted Answer · 2017-05-03 13:24:31Z

1

Just like the error says, you can't perform an aggregate function (sum() in this case on a subquery). Instead, join that other table in on your FROM clause:

SELECT 
    Sum(IsNull(CircuitCourtFeeCap, 0) + IsNull(CircuitCourtCourtTime, 0) + IsNull(CircuitCourtWaiverFee, 0) + IsNull(CircuitCourtExpenseFee, 0)) AS TotalApproved,
    Sum(IsNull(e.FeeAmountCap, 0)) AS TotalFeeCap,
    Sum(IsNull(ct.CourtTime, 0)) AS TotalCourtTime
FROM DocumentNames dn
    LEFT OUTER JOIN (SELECT DocumentNameID, sum(claimed) as CourtTime FROM [dbo].[CourtTimes] GROUP BY DocumentNameID) ct
        ON ct.DocumentNameID = dn.DocumentNameID
    LEFT OUTER JOIN Expenses e 
        ON dn.DocumentNameID = e.DocumentNameID;

Updated this answer to move the [CourtTimes] table join into a subquery so it could be pre-aggregated at the DocumentNameID level before join.

edited May 3, 2017 at 13:24

answered May 3, 2017 at 13:17

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Liberty Over a year ago

OK, sounds reasonable but I have a doubt. If I join it would it not create issues. For example CourtTimes contains 5 entries for each entry of DocumentNames. Would the Sum of CircuitCourtFeeCap be not counted duplicated then? Or would it only count once per DocumentName entry?

JNevill Over a year ago

It very well might. In the case that you have a 1:many issue where the join will cause an increase in records, then check out the first comment above. Moving the SUM() into your subquery will solve the issue without switching to a JOIN. I will update the answer to show how to do that in a subquery in the FROM clause as well.

Tim Liberty Over a year ago

Nick's answer did not work as well but your updated answer looks very promising and may well give me the result I need. Super man! You made my day

Collectives™ on Stack Overflow

Aggregate function Sum not working when specifying subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related