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

    Pregunta

  • Hi,

    I have been facing this group by error for the following procedure. It has to grouped by the Column AssetID and [Month].

    CREATE PROCEDURE [dbo].[AVGCycleTimeSpudtoTD1]
    (  
     
     @WDPMeasureID int
     
    )
    AS
     
    Declare @Date int


    SET @Date =  (Select Count(*) from
                 (Select [MONTH] from dbo.WDPWfFactTable A1
                 Inner Join [dbo].[WDPMeasuresConfigTable] A2
    ON A1.WDPID = A2.WDPID and A1.AssetID = A2.AssetID
    where WdpMeasureID = (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                          Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG')
    Except Select  [MONTH]  from dbo.AvgWDPWfFactTable
    Where WDPID = (Select WDPID from dbo.WDPMeasuresConfigTable
                   where WdpMeasureID = @WDPMeasureID)
                   and AssetID = (Select AssetID from dbo.WDPMeasuresConfigTable
                                  where WdpMeasureID = @WDPMeasureID))a)
     

    IF (@Date > 0)
    Begin
      Insert into dbo.AvgWDPWfFactTable
      Select
      AssetID = (Select AssetID from [dbo].[WDPMeasuresConfigTable]
                 where WdpMeasureID = @WDPMeasureID),
      AVG(ValueInDays) as AverageValue,
      WDPID = (Select WDPID from [dbo].[WDPMeasuresConfigTable]
               where WdpMeasureID = @WDPMeasureID),
      GETDATE() as [DateTime],
      UnitOfMeasure = (Select UnitOfMeasure from [dbo].[WDPMeasuresConfigTable]
                       where WdpMeasureID = @WDPMeasureID),
      MeasureLevel = (Select MeasureLevel from [dbo].[WDPMeasuresConfigTable]
                       where WdpMeasureID = @WDPMeasureID),
      B.[MONTH] ,                           
      MeasureName = (Select MeasureName from [dbo].[WDPMeasuresConfigTable]
                     where WdpMeasureID = @WDPMeasureID )
      from dbo.WDPWfFactTable B
      Inner join [dbo].[WDPMeasuresConfigTable] as A
      ON B.WDPID = A. WDPID and B.AssetID = A. AssetID
      where A.WdpMeasureID = (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG' Group By B.[MONTH])
                                
      and B.[Month] in   (Select * from (Select [MONTH] from dbo.WDPWfFactTable B
                          Inner Join [dbo].[WDPMeasuresConfigTable] A
                          ON B.WDPID = A.WDPID and B.AssetID = A.AssetID
    where WdpMeasureID = (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG')
                             
                             
    Except Select  [MONTH]  from dbo.AvgWDPWfFactTable Where WDPID = (Select WDPID from dbo.WDPMeasuresConfigTable
                                                                    where WdpMeasureID = @WDPMeasureID)
                                                       and AssetID = (Select AssetID from dbo.WDPMeasuresConfigTable
                                                                    where WdpMeasureID = @WDPMeasureID))a 
            Group by B.AssetID,B.WDPID,B.[MONTH]  
     )
    END

     

     

    Any Help would be Appreciated.

    Thanks

    TOM.

    jueves, 06 de octubre de 2011 15:57

Respuestas

  • Your query has

    Inner join [dbo].[WDPMeasuresConfigTable] as A
      ON B.WDPID = A. WDPID and B.AssetID = A. AssetID
      where A.WdpMeasureID = (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG' Group By B.[MONTH])
    

    You are not allowed to group the inner subquery

    (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG' Group By B.[MONTH])
    


    by columns in the outer query.  Doing that would make no sense, since the inner query is run once for every row in the outer query.  Which means there is only one value of B.[MONTH] each time you run the inner query. 

    Even if this wasn't an error, you would get an error from the GROUP BY since you are not grouping by Measure1, but you select Measure1 without an aggregate function.

    Tom

    • Marcado como respuesta KJian_ jueves, 13 de octubre de 2011 6:31
    jueves, 06 de octubre de 2011 16:30

Todas las respuestas

  • Your query has

    Inner join [dbo].[WDPMeasuresConfigTable] as A
      ON B.WDPID = A. WDPID and B.AssetID = A. AssetID
      where A.WdpMeasureID = (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG' Group By B.[MONTH])
    

    You are not allowed to group the inner subquery

    (Select Measure1 from [dbo].[WDPMeasuresConfigTable]
                              Where WdpMeasureID = @WDPMeasureID and Operator = 'AVG' Group By B.[MONTH])
    


    by columns in the outer query.  Doing that would make no sense, since the inner query is run once for every row in the outer query.  Which means there is only one value of B.[MONTH] each time you run the inner query. 

    Even if this wasn't an error, you would get an error from the GROUP BY since you are not grouping by Measure1, but you select Measure1 without an aggregate function.

    Tom

    • Marcado como respuesta KJian_ jueves, 13 de octubre de 2011 6:31
    jueves, 06 de octubre de 2011 16:30
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible AND not local dialect.

     

    Sample data is also A good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask A question ON A Newsgroup, look at: http://www.catB.org/~esr/faqs/smart-questions.html

    In spite of your rudeness, i will try to guess and clean up your code as to what you might have meant if you were polite. You missed a lot of best practices. Why did you have a vague, useless “@date” as an integer? Hey, let's call it a “@local_stupid_non_date_number” so people can read it. Since a DATE is 3 bytes and an integer is 4 byres, this does not even make sense in physical terms. Have you mixed meta data like units of measure into the schema?

    Do you have any idea how hard it is to compile stuff like this nested mess?

    SELECT asset_id = (SELECT asset_id FROM Wdp_Measures_Config ..).

    You do you actually like using reserved words? Vague data element names? Where are the semi-colons you actually put “-table” as an affix! Why do you think that A and B are good names for sub query expression?

    This looks like a badly designed data warehouse with fact tables, but we have no DDL. I spent 30 minutes on this and based on many decades with SQL, I think you have serious problems. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    jueves, 06 de octubre de 2011 21:52