Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

  • Hello,

    In brief, the query below is generating the error given in the post subject line. I have read dozens of posts on this particular error and they typically seem to resolve to having a constant of some sort in the GROUP BY expression, which is not the case with my GROUP BY clause.

    In short, what I'm trying to do with this query is as follow:

    I have purchase order tables comprised of the usual header (PO_MX) and detail (PO_DX) tables, linked by PONumber. The columns of interest in these tables are, in the header: a PaymentPosted field, an MDEP code field, and a PrimarySplit percentage field. In the detail section we have a QuanityOrdered field and a Price field.

    For all purchase orders having the same MDEP code, the following calculation has to be made: Calculate the total amount of the purchase order as the sum of all (QuantityOrdered * Price). Then, subtract the PaymentPosted amount from the header section. This yields the unpaid portion of the purchase order. Then multiply this value by the percentage (expressed as an integer) housed in the MDEP column from the header.

    Once this sum has been calculated for all purchase orders having the same MDEP code, this value then needs to be written to the MDEP table. The MDEP table has, in each row, an MDEP column and a POobligations column.

    So, for each set of purchase orders with the same MDEP code, the above calculation has to be made and the resultant figure posted to the proper row of the MDEP table.

    If I remove the outermost part of the query, the part that pertains just to updating the MDEP table, and modify the line, "where PO_MX.MDEP = MDEP.MDEP" to read "where PO_MX.MDEP = 'somespecific MDEP code', the query works perfectly. That is, it correctly calculates the correct sum to post to the MDEP table for all purchase orders with the given MDEP code.

    As soon as I encapsulate this query within the first two and last two lines of code, I get the error described pointing to the "group by PO_DX.PONumber" line of code.

    I would greatly appreciate any suggestions regarding this error.

    Thank You

    --------------------------------------------------------------------------------------------------------------

    Update MDEP

    Set MDEP.POObligations =

    (

    select SUM(PerPO.SumOfPurchaseOrder) from

    (

    Select PO_MX.PONumber, PO_MX.MDEP, ((PO_MX.PaymentPosted * -1 ) + POD.SumOfLineItems) * (cast(PO_MX.PrimarySplit as decimal(5,2))/100) As SumOfPurchaseOrder

    from PO_MX

    inner join

    (

    select PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) As SumOfLineItems

    from PO_DX

    where (QuantityOrdered * Price) is not NULL

    group by PO_DX.PONumber

    )

    as POD

    on PO_MX.PONumber = POD.PONumber

    where PO_MX.MDEP = MDEP.MDEP

    )

    as PerPO

    )

    where MDEP.MDEP In

    (select MDEP from PO_MX)

  • It may be that you haven't defined MDEP within the select - it's having to call all the way up to the UPDATE statement to find it, and that's outside of the GROUP range. What happens if you add MDEP into the the query, like this:

    Update MDEP

    Set MDEP.POObligations =

    (

    select SUM(PerPO.SumOfPurchaseOrder) from

    (

    Select PO_MX.PONumber, PO_MX.MDEP, ((PO_MX.PaymentPosted * -1 ) + POD.SumOfLineItems) * (cast(PO_MX.PrimarySplit as decimal(5,2))/100) As SumOfPurchaseOrder

    from PO_MX

    inner join

    (

    select PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) As SumOfLineItems

    from PO_DX

    join MDEP ON PO_MX.MDEP = MDEP.MDEP --add MDEP as a join here

    where (QuantityOrdered * Price) is not NULL

    group by PO_DX.PONumber

    )

    as POD

    on PO_MX.PONumber = POD.PONumber

    --where PO_MX.MDEP = MDEP.MDEP --move this to the ON clause above

    )

    as PerPO

    )

    where MDEP.MDEP In

    (select MDEP from PO_MX)

  • Doug,

    Thank you for your reply. The SQL compiler was not happy with the join clause movement that you had proposed. Nonetheless, your suggestion got me to thinking about a new approach to this query. The result is shown below. The result is quite a bit more compact and, to my eyes at least, a good bit more readable than my first version. The query below runs and it generates the correct results when tested on a modest size data set. Using "Sum(Distinct...)" on columns I'm not actually summing to avoid "Group By" protestations seems a bit tacky, but it works, and as far as I can see, should never cause any problems. Anyhow, apart from adding a little further conditioning to the inner where clause, I believe this will do it for me.

    ***Actually, check that. Just before hitting post I took one last look at the query and believe I have an error both in it and my testing methodology. I'll have to take a closer look tomorrow...

    Thanks again -

    UPDATEMDEP

    SetMDEP.POObligations =

    (

    Select (SUM(PO_DX.QuantityOrdered * PO_DX.Price)- SUM(Distinct PO_MX.PaymentPosted)) * SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))

    from PO_MX

    inner join PO_DX on PO_DX.PONumber = PO_MX.PONumber

    Where PO_MX.MDEP = MDEP.MDEP

    )

    WHEREMDEP.MDEP IN

    (Select MDEP From PO_MX)

  • After some further experimentation I've gotten close (or so it seems) but not quite there. My latest attempt at this is shown below. The inner query (starting with Select (D.LineItemTotal) and ending with (Where M1.MDEP = MDEP.MDEP) works perfectly when run by itself and the final where clause is assigned a literal value (i.e., Where M1.MDEP = 'abcd'). This inner query will calculate a correct numeric value for each Purchase Order having an MDEP value matching that in the Where clause.

    Now, the final step would be to bracket this inner query with an Update MDEP statement similar to the one shown below, that assigns the sum of the inner query results to the row in the MDEP table with the matching MDEP column. Regardless of how I approach this final step, I run into one error or another.

    If anyone has any thoughts on how to approach this I would be most appreciative.

    Update MDEP

    Set MDEP.POObligations = SUM

    (

    Select (D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent As POTotal from

    (

    (select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1

    inner join

    (select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber

    inner join

    (select PONumber, SUM(QuantityOrdered * Price) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber

    )

    Where M1.MDEP = MDEP.MDEP

    )

    Where MDEP IN (Select MDEP From PO_MX)

  • UPDATE m Set

    POObligations = x.POObligations

    FROM MDEP m

    CROSS APPLY (

    SELECT

    POObligations = (SUM(PO_DX.QuantityOrdered * PO_DX.Price) - SUM(Distinct PO_MX.PaymentPosted))

    * SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))

    FROM PO_MX

    INNER JOIN PO_DX

    ON PO_DX.PONumber = PO_MX.PONumber

    WHERE PO_MX.MDEP = m.MDEP

    ) p

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris,

    Thank you very much for your posted query. Unfortunately it overlooks one condition that I may not have made as clear as I should have. In the overall calculation that has to be made for each purchase order it cannot be assumed that the PrimarySplit percentage is the same for all Purchase Orders having the same MDEP code. That is, for all those purchase orders with MDEP code 'ABCD', for example, there might be some with a PrimarySplit value of 100%, some with 75%, others with 50%.

    In my test data set, in those cases where all purchase orders with a given MDEP code happened to have the same percentage, the query worked perfectly. For those purchase orders of a different MDEP that involved varying percentages, the query failed.

    If you have further suggestions of how to handle this last obstacle in the query I would be glad to give it a try.

    (By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)

  • Chris,

    After some further experimentation I appear to have a working solution as shown below. Basically I adopted an inner query that I had tried from a previous attempt to the outer query of your suggestion. In the testing I've done so far it has calculated all the sums correctly. I'll do a bit of further testing tomorrow, but this seems to be a working solution.

    Thanks again,

    UPDATE m Set

    POObligations = p.POTotal

    FROM MDEP m

    CROSS APPLY (

    Select sum((D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent) As POTotal from

    (

    (select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1

    inner join

    (select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber

    inner join

    (select PONumber, SUM(isnull(QuantityOrdered,0) * isnull(Price,0)) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber

    )

    Where M1.MDEP = m.MDEP

    ) p

  • Dale Magnant (2/27/2014)


    ...(By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)

    Yep, sorry, my mistake.

    Dale Magnant (2/27/2014)


    Chris,

    After some further experimentation I appear to have a working solution as shown below. Basically I adopted an inner query that I had tried from a previous attempt to the outer query of your suggestion. In the testing I've done so far it has calculated all the sums correctly. I'll do a bit of further testing tomorrow, but this seems to be a working solution.

    Thanks again,

    UPDATE m Set

    POObligations = p.POTotal

    FROM MDEP m

    CROSS APPLY (

    Select sum((D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent) As POTotal from

    (

    (select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1

    inner join

    (select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber

    inner join

    (select PONumber, SUM(isnull(QuantityOrdered,0) * isnull(Price,0)) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber

    )

    Where M1.MDEP = m.MDEP

    ) p

    I think you can simplify this to the following:

    UPDATE m SET

    POObligations = p.POTotal

    FROM MDEP m

    CROSS APPLY (

    SELECT

    POTotal = SUM((dx.LineItemTotal - mx.PaidToDate) * CAST(mx.PrimarySplit AS DECIMAL(5,2))/100)

    FROM PO_MX mx

    INNER JOIN (

    SELECT

    PONumber,

    LineItemTotal = ISNULL(SUM(QuantityOrdered * Price),0)

    FROM PO_DX

    GROUP BY PONumber

    ) dx

    ON mx.PONumber = dx.PONumber

    WHERE mx.MDEP = m.MDEP

    ) p

    - because neither of the two subselects of PO_MX perform any aggregation.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris,

    I tested your latest post and yes, it does indeed calculate all sums correctly using the same test data set. There was one minor typo I wanted to mention for others who might be following along: the expression "dx.LineItemTotal - mx.PaidToDate" needed changing to "dx.LineItemTotal - mx.PaymentPosted".

    Your original post introduced me to the 'Cross Apply' expression which I had never previously used. Rummaging around on the web I found the following article to be helpful in understanding it a bit further;

    http://sqlserverplanet.com/sql-2005/cross-apply-explained

    Again, thank you very much for the responses to my post - it is very much appreciated.

  • Any time Dale.

    Check out the two APPLY articles by Paul White in my signature too, they are an excellent walkthrough with examples.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply