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.