Group by behaviour in a subquery

• Bhalchandra

  SSC Enthusiast

  Points: 146

  More actions

  March 1, 2005 at 10:55 pm

  #65456

  Hi,

  I have  the following query which works and a similar query with fields of slightly different datatype that does not work. The query that works is pasted below:

  select  a.budgetyear , budgetedcost = sum(a.budgetedcost) -

      (select sum(b.budgetedcost) from budget b where b.budgetyear = a.budgetyear + 1 )

  from   budget a

  group by a.budgetyear

  In the above query the budgetyear column is of type Int.

  The query does not work is as follows:

  select  datepart(yy,a.readingdate) , sum(a.overalltotal)  - 

   (select  sum(b.overalltotal)  from cost b

     where datepart(yy,b.readingdate)  = datepart(yy,a.readingdate) + 1)

  from   cost a

  group by datepart(yy,a.readingdate)

  The error that appears for the above query is:

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

  In the above query the readingdate field is of type datetime.

  Also the above query works if the +1 is removed from the subquery.

  Please help.

  Brlele

• Jamie Thomson

  SSChampion

  Points: 11805

  More actions

  March 3, 2005 at 2:12 am

  #545130

  The problem is that 'a.ReadingDate' is grouped in the outer query but not in the correlated sub-query. The correlated sub-query can't join to 'a.ReadingDate' in the outer query because (in simple terms) it doesn't know which record to join to.

  Try this:

  select a.datepart(yy,a.readingdate) sum(a.overalltotal - b.overalltotal)

  from costa a

  inner join costb b

  on a.datepart(yy,a.readingdate) = b.datepart(yy,a.readingdate)

  group by a.datepart(yy,a.readingdate)

   

  Does that work?

   

  Jamie Thomson
  http://sqlblog.com/blogs/jamie_thomson