[SQL] Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Problem:

select avg(count(ip)) from pagehits where [month] = 2 group by ip

will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn't support it.

Solution - use a derived table:

select avg(ipcount) from (select count(ip) ipcount from pagehits where [month] = 2 group by ip) as sub

I'm posting this because searches on the error message didn't return good results, so if someone else has this problem (read: when I forget this again) this may save some frustration.


19 Comments

  • To save some time, post your question on SQLTeam.com. You should get an answer quickly if you've provided enough information. Don't forget to subscribe to the thread so that you know when an answer has been posted.

  • Hi,



    Had very similar problem - in my case it was sum, instead of avg and your solution obviously worked here as well, tx a lot!

  • I had been trying to get the average of two summed columns (weighted average) and this was a great solution.

    Thanks!
    -Mike

  • Thanks a bunch mate, I wasted loads of time trying to figure this one out.

  • Worked here too - good work fella!

  • I am using a reporting software called Showcase Strategy and am ecountering the same message. The software uses a different type of select statement.

    I am trying to get an Average for the statement below, any ideas on how I can avoid the error?

    CAST( TIMESTAMPDIFF( SQL_TSI_DAY, MIN( event_dt ), MAX( work_dt ) ) AS real )

  • Just great, where can I dig it? =) Serious this saved me a lot of time and it was the first hit on google.

    Thanks

  • This helped me really... Thanks..

  • ver y thanks great!

  • Great job, I had a similar problum (with SUM) and this got it working great!!!

  • Great! I had similar problem, I was using Sum on calculated weight and volume, selecting from other subquery , Used this Worked Great for me.

    -Srini

  • "Usually the simplest solution is the most elegant"

    (...with apologies to Occam)

    Thanks!

  • "Usually the simplest solution is the most elegant"

    (...with apologies to Occam)

    Thanks!

  • Great .......thnx a ton

  • select sum(datediff(s,att_date,(select in_time from sys_para where tran_date between '2009/04/01' and '2010/03/31'))),out_time from attendance

  • I've been using this technique for awhile but went looking for another way about doing this in SQL. See code below. Is there another way someone has devised that will give the same result? This is a small part of a larger query and it does not like it when I use the nested query. Any thoughts would be appreciated. This query only returns one value.
    Query:
    SELECT SUM(TEST) as SamplePct
    FROM(
    SELECT LB.Pct*(((LB.AmtBud /(
    (SELECT CASE WHEN PR.ProjectType='FP'
    THEN ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)
    WHEN PR.ProjectType='TM'
    THEN ISNULL(SUM(ISNULL(LB.BillBud,0.00)),0.00)
    ELSE ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)
    END
    FROM LB WHERE LB.WBS1=PR.WBS1)
    +
    (SELECT CASE WHEN PR.ProjectType='FP'
    THEN ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)
    WHEN PR.ProjectType='TM' THEN ISNULL(SUM(ISNULL(EB.BillBud,0.00)),0.00)
    ELSE ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)
    END
    FROM EB
    left outer join PR as PR1 on EB.WBS1=PR1.WBS1 AND EB.WBS2=PR1.WBS2 AND EB.WBS3=PR1.WBS3
    WHERE EB.WBS1=PR.WBS1 AND EB.Account!='4020.00')))*100)/100)
    as TEST
    FROM LB
    left outer join PR on LB.WBS1=PR.WBS1 AND LB.WBS2=PR.WBS2 AND LB.WBS3=PR.WBS3
    WHERE PR.WBS1='05012-11'
    UNION ALL
    SELECT EB.Pct*(((
    EB.AmtBud /((SELECT CASE WHEN PR.ProjectType='FP'
    THEN ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)
    WHEN PR.ProjectType='TM'
    THEN ISNULL(SUM(ISNULL(LB.BillBud,0.00)),0.00)
    ELSE ISNULL(SUM(ISNULL(LB.AmtBud,0.00)),0.00)
    END
    FROM LB WHERE LB.WBS1=PR.WBS1)
    + (SELECT CASE WHEN PR.ProjectType='FP'
    THEN ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)
    WHEN PR.ProjectType='TM' THEN ISNULL(SUM(ISNULL(EB.BillBud,0.00)),0.00)
    ELSE ISNULL(SUM(ISNULL(EB.AmtBud,0.00)),0.00)
    END
    FROM EB
    left outer join PR as PR1 on EB.WBS1=PR1.WBS1 AND EB.WBS2=PR1.WBS2 AND EB.WBS3=PR1.WBS3
    WHERE EB.WBS1=PR.WBS1 AND EB.Account!='4020.00')))*100)/100)
    as TEST
    FROM EB
    left outer join PR on EB.WBS1=PR.WBS1 AND EB.WBS2=PR.WBS2 AND EB.WBS3=PR.WBS3
    WHERE PR.WBS1='05012-11'
    )
    as TESTTABLE

    Thanks in advance ~CW

  • Helped me a lot!!!
    Great work.Thank You.

  • Thanks, this was really helpful.

  • Select CashAmount=Sum(Case When P.PayModeCode=(select paymodecash from pos_mastercode) THEN P.Amount END)
    pls help

Comments have been disabled for this content.