Post reply

error on group by clause - Each GROUP BY expression must contain at least one column that is not an outer reference.

  • March 30, 2012 at 8:20 am

    #258426

    I am using the AdventureWorks DB in SQL 2008 and have the following code:

    select sh.SalesPersonID,YEAR(sh.OrderDate)as 'OrderYear', SUM(sd.OrderQty*sd.UnitPrice)as 'CostOfOrder'

    from sales.SalesOrderHeader as sh

    inner join Sales.SalesOrderDetail as sd

    on sh.SalesOrderID = sd.SalesOrderID

    group by sh.SalesPersonID,'orderYear','costOfOrder'

    having sh.SalesPersonID is not null

    order by sh.SalesPersonID

    I am wanting to return the total cost of the orders for each sales person by year.

    I can't figue out what I need to do to not get the abovementioned error. If I change the group by clause to use the orderdate column (which is the field I extract the year) from the query works however does not give me the summary I am looking for.

    Appreciate any insight or thoughts.

    Thanks

  • March 30, 2012 at 8:39 am

    #1466978

    i think that syntax was valid for 2000, but not 2005 and above;

    as i remermber it the solution is simple...

    include the sd.SalesOrderId in the select, and

    i believe you have to change the group by to SalesOrderDetail.SalesOrderId instead of SalesOrderHeader.SalesOrderId

    aliased as

    ( sd.SalesOrderId instead of sh.SalesOrderId )

    one of those new tighhter rules for SQL 2005, i had a few queries that did the same thing when we migrated.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 30, 2012 at 8:50 am

    #1466991

    You are grouping by string literals which will not work.

    Try this.

    select sh.SalesPersonID,YEAR(sh.OrderDate)as 'OrderYear', SUM(sd.OrderQty*sd.UnitPrice)as 'CostOfOrder'

    from sales.SalesOrderHeader as sh

    inner join Sales.SalesOrderDetail as sd

    on sh.SalesOrderID = sd.SalesOrderID

    group by sh.SalesPersonID

    having sh.SalesPersonID is not null

    order by sh.SalesPersonID

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 30, 2012 at 8:51 am

    #1466993

    Thanks,

    I have just changed the group by clause to use:

    year(sh.orderdate) rather than using the alias created in teh select statement and this has resolved the problem.

    Thanks for your reply.

Viewing 4 posts - 1 through 4 (of 4 total)

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