Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error: Each GROUP BY expression must contain at least one column that is not an outer reference. Expand / Collapse
Author
Message
Posted Monday, February 24, 2014 3:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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)
Post #1544735
Posted Tuesday, February 25, 2014 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:41 PM
Points: 149, Visits: 3,566
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)

Post #1545062
Posted Tuesday, February 25, 2014 5:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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 -


UPDATE MDEP
Set MDEP.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
)
WHERE MDEP.MDEP IN
(Select MDEP From PO_MX)
Post #1545185
Posted Wednesday, February 26, 2014 6:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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)
Post #1545655
Posted Thursday, February 27, 2014 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1545758
Posted Thursday, February 27, 2014 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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.)

Post #1546065
Posted Thursday, February 27, 2014 4:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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
Post #1546164
Posted Friday, February 28, 2014 1:57 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1546225
Posted Friday, February 28, 2014 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:45 AM
Points: 17, Visits: 34
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.
Post #1546375
Posted Friday, February 28, 2014 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1546384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse