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