Column 'x' is invalid in the select list because it is not contained in either an aggregate function

• Dan Guzman - Not the MVP

  Hall of Fame

  Points: 3757

  More actions

  April 3, 2015 at 9:47 am

  #306453

  I feel like this should have been answered before, but I just couldn't find it. I have a specific variation on the standard 'Column Invalid' question:

  I have this query that works fine:

  SELECT vd.Question ,

  csq.Q# ,

  csq.Q_Sort ,

  csq.Q_SubSort ,

  AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,

  vd.RType

  FROM dbo.vwData AS vd

  INNER JOIN dbo.CompSurvey_Questions AS csq ON csq.FieldName = vd.Question

  GROUP BY vd.Question ,

  csq.Q# ,

  csq.Q_Sort ,

  csq.Q_SubSort ,

  vd.RType

  When I add this second average column like this:

  SELECT vd.Question ,

  csq.Q# ,

  csq.Q_Sort ,

  csq.Q_SubSort ,

  AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,

  AVG(CAST(vd.Response AS FLOAT)) OVER (PARTITION BY csq.Q_Sort) AS AvgG ,

  vd.RType

  FROM dbo.vwData AS vd

  INNER JOIN dbo.CompSurvey_Questions AS csq ON csq.FieldName = vd.Question

  GROUP BY vd.Question ,

  csq.Q# ,

  csq.Q_Sort ,

  csq.Q_SubSort ,

  vd.RType

  I get the error:

  Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible? What am I doing wrong?

  Dan

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  April 3, 2015 at 10:35 am

  #1788617

  Quick thought, you cannot mix group by and window function (over clause), not compatible!

  😎