Sunday, April 24, 2011

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

Hi All,

Few days back I was stuck a bit with a SQL problem, I got it resolved with the suggestion of one of my colleagues(Anup). I have to access record  from SQL SERVER 2008 based on an aggregate function. But I got error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Structure of my table was somewhat like below:


What I wanted from this table was records per UserId where CreatedDate is equal to minimum CreatedDate. So first I framed a query as below:

SELECT UserID, MIN(CreatedDate)
FROM RenewalHistory
Group By UserID
           (QUERY  1)
This worked fine giving me the expected results and then I added column SubscriptionType to select list, making my query like below:

SELECT UserID, MIN(CreatedDate), SubscriptionType
FROM RenewalHistory
Group By UserID
           (QUERY  2)
This started giving me error saying Column 'RenewalHistory.SubscriptionType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

To overcome this, I used table produced in query 1 as a sub-table and reused it to get desired record as shown in below query:

SELECT rh.Id, rh.UserId, rh.SubscriptionType, rh.Createddate
FROM RenewalHistory rh, (
      SELECT UserId, MIN(CreatedDate) AS CreatedDate
      FROM RenewalHistory
      Group By UserID) subRH
WHERE rh.UserId = subRH.UserId AND
rh.CreatedDate = subRH.CreatedDate
           (QUERY  3)

This was how I got my problem solved. But I feel this is not the best way to sort out this problem, so if you have some better solution for this problem. Then kindly share it here.

4 comments:

  1. how to solve when it is with joins

    ReplyDelete
    Replies
    1. Well the solution will remain almost same. Please elaborate your scenario for more.

      Delete
  2. SELECT UserID, MIN(CreatedDate), SubscriptionType
    FROM RenewalHistory
    Group By UserID,SubscriptionType

    ReplyDelete
  3. You should include all the columns in select except the agreegate function in group by clause.
    Solution (http://technicalsmile.blogspot.com/2011/04/column-is-invalid-in-select-list.html?showComment=1351756471273#c3374328102852852477) given by abm.spirit is the best way to do it.

    ReplyDelete