Group By in a sub query

Question

Group By in a sub query

solus

Hall of Fame

Points: 3495
More actions
November 5, 2018 at 3:18 am

#378553

I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Can you provide the entire query?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

solus Hall of Fame Points: 3495 More actions · Answer 2

apologies, here's the complete sql code

select Trade.tradedate AS TradeDate, Trade.status, contract.contract AS ContractID, fee.feemode, position.counterparty, position.positiontype, Trade.cstcontractstart AS ContractStartDate, Trade.cstcontractend as ContractEndDate, contract.contracttype, CONVERT(int,Trade.trade) AS TradeID, feetimeperiod.feetimeperiod, feetimeperiod.dbcolumn, feetimeperiod.dbvalue, feetimeperiod.begtime AS FeeTimePeriodBeginTime, feetimeperiod.endtime AS FeeTimePeriodEndTime, loadshapeprofile.begtime As StartDateTime, loadshapeprofile.endtime AS EndDateTime, loadshapeprofile.offset, loadshape.timeunit, CASE WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) = 1 THEN 'D' WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 1 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 31 THEN 'M' WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 31 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 93 THEN 'Q' WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 93 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 183 THEN 'S' ELSE 'Y' END AS BlockDescription, position.block, CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS FLOAT) * 24.0 AS HoursInPeriod, CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS int) AS Days, position.unit, ISNULL(fee.pricediff,0) AS GBPMWh, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he1 / 2 ELSE loadshapeprofile.he1 END AS loadshapeprofilehe1, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he2 / 2 ELSE loadshapeprofile.he2 END AS loadshapeprofilehe2, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he3 / 2 ELSE loadshapeprofile.he3 END AS loadshapeprofilehe3, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he4 / 2 ELSE loadshapeprofile.he4 END AS loadshapeprofilehe4, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he5 / 2 ELSE loadshapeprofile.he5 END AS loadshapeprofilehe5, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he6 / 2 ELSE loadshapeprofile.he6 END AS loadshapeprofilehe6, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he7 / 2 ELSE loadshapeprofile.he7 END AS loadshapeprofilehe7, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he8 / 2 ELSE loadshapeprofile.he8 END AS loadshapeprofilehe8, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he9 / 2 ELSE loadshapeprofile.he9 END AS loadshapeprofilehe9, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he10 / 2 ELSE loadshapeprofile.he10 END AS loadshapeprofilehe10, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he11 / 2 ELSE loadshapeprofile.he11 END AS loadshapeprofilehe11, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he12 / 2 ELSE loadshapeprofile.he12 END AS loadshapeprofilehe12, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he13 / 2 ELSE loadshapeprofile.he13 END AS loadshapeprofilehe13, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he14 / 2 ELSE loadshapeprofile.he14 END AS loadshapeprofilehe14, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he15 / 2 ELSE loadshapeprofile.he15 END AS loadshapeprofilehe15, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he16 / 2 ELSE loadshapeprofile.he16 END AS loadshapeprofilehe16, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he17 / 2 ELSE loadshapeprofile.he17 END AS loadshapeprofilehe17, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he18 / 2 ELSE loadshapeprofile.he18 END AS loadshapeprofilehe18, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he19 / 2 ELSE loadshapeprofile.he19 END AS loadshapeprofilehe19, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he20 / 2 ELSE loadshapeprofile.he20 END AS loadshapeprofilehe20, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he21 / 2 ELSE loadshapeprofile.he21 END AS loadshapeprofilehe21, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he22 / 2 ELSE loadshapeprofile.he22 END AS loadshapeprofilehe22, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he23 / 2 ELSE loadshapeprofile.he23 END AS loadshapeprofilehe23, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he24 / 2 ELSE loadshapeprofile.he24 END AS loadshapeprofilehe24, CASE WHEN loadshape.timeunit = 'HALFHOUR' THEN loadshapeprofile.he25 / 2 ELSE loadshapeprofile.he25 END AS loadshapeprofilehe25 from Trade inner join position on trade.trade = position.trade inner join contract on position.contract = contract.contract inner join loadshape on position.loadshape = loadshape.loadshape inner join loadshapeprofile on loadshape.loadshape = loadshapeprofile.loadshape inner join fee on position.position = fee.dbvalue inner join feetimeperiod on fee.feetimeperiod = feetimeperiod.feetimeperiod and feetimeperiod.dbvalue = position.position where contract.contract = '1111111' and position.loadshape is not null and fee.dbcolumn = 'POSITION' and fee.feemethod = 'COMMODITY PRICE' and (CAST(DATEPART(MINUTE, feetimeperiod.begtime ) as decimal) = loadshapeprofile.offset) and Trade.status = 'ACTIVE' and Trade.tradestatus IN ('FO Approval','TC Approval') and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod, loadshapeprofile.offset) and feetimeperiod.begtime >= '2017-03-01 00:00:00.000' and CONVERT(date,feetimeperiod.endtime) <= '2017-03-31' order by feetimeperiod.begtime,feetimeperiod.endtime

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

That subquery is rather confusing. Can you explain what that part of the query is trying to do?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

solus Hall of Fame Points: 3495 More actions · Answer 4

Im matching up the 2 begtime columns, but within the matches found are 30 minute periods. (up to 48) I only need 2 of these so I figured if I grouped by the offset (which is 0 - on the hour or 30 - on the half hour) that will eliminate all the time variations for each day effectively giving me 2 feetimeperiod values in the subquery. There may not always be 1 '0' offset and 1 '30' offset so I cant use TOP 2. I cant think of any other way to eliminate the other unwanted time periods for the same date

drew.allen SSC Guru Points: 76988 More actions · Answer 5

solus - Monday, November 5, 2018 3:18 AM
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery

loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

solus Hall of Fame Points: 3495 More actions · Answer 6

How would I be able to achieve the results I'm after ? This has had me stumped for quite a while now

drew.allen SSC Guru Points: 76988 More actions · Answer 7

solus - Monday, November 5, 2018 7:37 AM
How would I be able to achieve the results I'm after ? This has had me stumped for quite a while now

You've been around long enough to know that we always ask for sample data and expected results. The first link in my signature shows you how to provide it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

solus - Monday, November 5, 2018 3:18 AM
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery

This isn't going to do what you're expecting: you're not going to get MAX(feetimeperiod) if you GROUP BY feetimeperiod.

^{“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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

drew.allen - Monday, November 5, 2018 7:32 AM
solus - Monday, November 5, 2018 3:18 AM
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew

With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Jeff Moden - Monday, November 5, 2018 9:04 AM
drew.allen - Monday, November 5, 2018 7:32 AM
solus - Monday, November 5, 2018 3:18 AM
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. 😀

It is numberplate-compliant though 🙂

^{“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

solus Hall of Fame Points: 3495 More actions · Answer 11

Jeff Moden - Monday, November 5, 2018 9:04 AM
drew.allen - Monday, November 5, 2018 7:32 AM
solus - Monday, November 5, 2018 3:18 AM
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. 😀

this is the world I have to deal with, not my database structure 🙂