--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
>
> >
> > 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
>

Great!!! Your Solution Worked, I didn't use Order by.

Thanks

Muthu