A quick solution that treats January and February as belonging to the previous year would be:

select Categories_Name, Cancelreasons_Name,
extract(year from Cancel_Date) - case when extract(month from Cancel_Date) < 3 then 1 else 0 end as Stats_Year,
count(Cancelreasons_ID) as Count_Reasons, sum(CancelAccount) as SUM_Oustanding
from V_Register_Cancelled
group by Categories_Name, Cancelreasons_Name, Stats_Year

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of nols_smit
Sent: 1. november 2010 14:10
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Aggregate fuction extract

Hi,

With FireBird 2.1.3, I use the select statement displayed below to extract some summary statistics. Our Financial year is from 1 March to end of February. How must I modify the select statement below to extract summary statistics per financial year?

Regards,

Nols Smit

===================================================================
select Categories_Name, Cancelreasons_Name, extract(year from Cancel_Date) as Stats_Year,
count(Cancelreasons_ID) as Count_Reasons, sum(CancelAccount) as SUM_Oustanding
from V_Register_Cancelled
group by Categories_Name, Cancelreasons_Name, Stats_Year