Group By in a sub query

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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)

  • 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

  • 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 πŸ™‚

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

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