none
cant perform an aggregate function with in the subquery

    Question

  • select sum((Month - avg(Month)) * ([Utilization] - avg([Utilization])) / sum((Month - avg(Month))) * (Month - avg(Month))) as slope
    from
    (
    select Month X , avg(Month) over () as x_bar,
           [Utilization] Y , avg([Utilization]) over () as y_bar
     From table ) s

    wts wrong with this query , can you correct this and send us back

    Thanks you!

     
    • Edited by Ychinnari Thursday, May 03, 2018 7:14 AM
    • Moved by Olaf HelperMVP Saturday, July 14, 2018 3:03 AM Moved from "Database Design" to a more related forum
    Thursday, May 03, 2018 7:13 AM

Answers

  • When you use Aggregation, then you have to add not aggregated columns to the GROUP BY clause, in this case column "Month "

    elect sum((Month - avg(Month)) * ([Utilization] - avg([Utilization])) / sum((Month - avg(Month))) * (Month - avg(Month))) as slope 
     from 
     (
     select Month X , avg(Month) over () as x_bar,
            [Utilization] Y , avg([Utilization]) over () as y_bar
      From table
      GROUP BY Month ) s


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 03, 2018 7:36 AM

All replies

  • can i ask what you're trying to calculate by the above expression?

    if possible, share the original formula


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 03, 2018 7:31 AM
  • When you use Aggregation, then you have to add not aggregated columns to the GROUP BY clause, in this case column "Month "

    elect sum((Month - avg(Month)) * ([Utilization] - avg([Utilization])) / sum((Month - avg(Month))) * (Month - avg(Month))) as slope 
     from 
     (
     select Month X , avg(Month) over () as x_bar,
            [Utilization] Y , avg([Utilization]) over () as y_bar
      From table
      GROUP BY Month ) s


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 03, 2018 7:36 AM
  • A side point. "sum((Month - avg(Month))" (the second one) has a redundant set of parenthesis.

    The calculation is:

    SUM
    (
     	(Month - AVG(Month))
     *	(Utilization - AVG(Utilization))
     /	SUM(Month - AVG(Month))
     *	(Month - AVG(Month))
    )

    Which means that

    (Month - AVG(Month))

    is being done twice. And since order makes no difference by multiplication, the expression is actually squared. Perhaps this is can be rewritten more clearly as:

    SUM
    (
     	SQUARE((Month - AVG(Month)))
     *	(Utilization - AVG(Utilization))
     /	SUM(Month - AVG(Month))
    )

    I get the feeling more can be done here, but i do not feel that comfortable with math right now.

    Thursday, May 03, 2018 1:10 PM
    Moderator