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

  • Anamika

    SSCertifiable

    Points: 6202

    Hi,

    i'm raising the summary tables by using the following query,

    INSERT INTO ClientTbl_SummarySalesMonthTY

    ([RetailerKey]

    ,[ProductLevel1]

    ,[AttributeValue]

    ,[Month]

    ,[Year]

    ,[SalesinUnits]

    ,[GrossSalesValueinDollars]

    ,[NetSaleValue]

    ,[StockOnHandinUnits]

    ,[StockOnHandinDollars]

    ,[StockOnOrderinUnits]

    ,[StockOnOrderinDollars]

    ,[ReplenishmentType]

    ,[ProductStatus])

    select p.Retailerkey

    ,'Category'

    ,p.Category

    ,c.MonthNumRetailYear

    ,c.RetailYear

    ,sum(f.[Quantity Sold])

    ,sum(f.[Gross Sales Value Incl. Tax])

    ,sum(f.[Net Sales Value Incl. Tax])

    ,sum(f.[Store Stock Units on Hand])

    ,sum(f.[Store Stock On Hand Value Excl. Tax])

    ,sum(f.[Store Stock Units on Order])

    ,sum(f.[Store Stock On Order Value Excl. Tax])

    ,p.ReplenishmentType

    ,P.ProductStatus

    from ClientAdmin.dbo.ClientTbl_DimProduct p

    inner join ClientAdmin.dbo.ClientTbl_DimCalendar c

    on p.retailerkey = c.retailerkey

    inner join ClientAdmin.dbo.ClientTbl_FactSalesStock f

    on p.productkey =f.productkey and p.retailerkey =f.retailerkey and c.calendarkey=f.calendarkey

    group by p.Retailerkey

    ,'Category'

    ,p.Category

    ,c.MonthNumRetailYear

    ,c.RetailYear

    ,p.ReplenishmentType

    ,P.ProductStatus

    and getting the following error

    Msg 164,

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

    Early help is highly appreciated.

    Thanks

    Regards

    Viji

  • Mark Cowne

    One Orange Chip

    Points: 26698

    Remove 'Category' from your GROUP BY clause

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Anamika

    SSCertifiable

    Points: 6202

    gr8 it works

    thanks a lot Mr. Mark

    Viji

  • sandeep.cs3

    Old Hand

    Points: 307

  • Cadavre

    SSC-Forever

    Points: 41582

    sandeep.cs3 (12/13/2011)


    Good info about this topic is discussed at:

    SNIP

    This thread is from 2008. . .


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • rshadman

    SSC Journeyman

    Points: 83

    and yet that link was still helpful to me today, in 2012 🙂

  • inevercheckthis2002

    SSCommitted

    Points: 1759

    ..and that link is no longer valid, in 2014

  • PringleEater

    SSC Enthusiast

    Points: 141

    but link is valid in 2016

  • zaguarman

    SSC Rookie

    Points: 27

    Link still alive and helping lost people in 2018 :laugh:

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

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