>
> I can't use your first solution as the period doesn't always starts
> with oct, as endusers might have different fiscal year.
>
> The Second solution, order by gives me the following error
>
> "Invalid expression in the ORDER BY clause (not contained in either
> an aggregate function or the GROUP BY clause)."

The problem is that you can't order by something when you have already
aggregated it together. Your example is 'nice', because you are only
selecting 12 months worth of data, but Firebird can not know that you
will never ask for more data.

Consider this

ReceiptDate Amount
----------- -----
1 Jan 2006 100
1 Feb 2006 100
1 Jan 2007 50

Your procedure would end up with

Month Total
----- -----
01 150
02 100

Now you ask it to order by Year then day? That is impossible, because
on one hand Jan must come before Feb because 1 Jan 2006 is before 1
Feb 2006. But at the same time, Jan must come after Feb because 1 Jan
2007 is after 1 Feb 2006.

The only way you can achieve yearwise-monthwise ordering is to include
the year in your grouping.

FOR SELECT FIRST 12 EXTRACT(YEAR FROM RECEIPTDATE), EXTRACT(MONTH FROM
RECEIPTDATE), COUNT(*), SUM(AMOUNT)
FROM RECEIPT
GROUP BY 1,2
ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH FROM
RECEIPTDATE)

Adam