I want to aggregate records by week of the year to get a sum of charges for that week. My problem is that although I can achieve a week number on the first part of my select statement, it throws a wobbly about it being in the group by clause. It also doesn't like it NOT being in the group by clause (surprise!). Code follows. Database is Firebird 1.5
Thanks in advance.
ps. I am trying to avoid stored procedures if I can.
extract(year from E.EENDDATE) as "Year",
(extract(yearday from E.EENDDATE) - extract(weekday from E.EENDDATE-1) + 7) / 7 as "Week",
sum(A.AMOUNTCHARGED) as "Value"
from CCREVENTS E
inner join CCRACC A on (E.CONTRACTNO = A.CONTRACTNO)
extract(year from E.EENDDATE),
(extract(yearday from E.EENDDATE) - extract(weekday from E.EENDDATE-1) + 7) / 7 -- it doesn't like this line having brackets and operators in it!