I recall that in FB 1.5.x you cannot use alias names in the group by
clause. Try ordinal numbers instead;

Group By 1, 2
Order By 1, 2

Did not test anything, though.

HTH,

/Dany


John Robson wrote:

> Thank you Peter,
>
> I'm grateful you spent the time looking at this and then took the trouble to
> answer. I tried your solution, but still got into a pickle. But using your
> idea though, I ended up with a statement like ...
>
> Select Distinct EXTRACT(YEAR FROM OrderDate) as YearNum,
> EXTRACT(Month FROM OrderDate) as MonthNum
> from orders
> left join Itemlist on (ItemList.OrderNo = Orders.OrderNo)
> Order By YearNum, MonthNum
>
> This works in the sense that the Statement executes successfully and I see
> some results, but if I throw in an aggregate like ...
>
> Select Distinct EXTRACT(YEAR FROM OrderDate) as YearNum,
> EXTRACT(Month FROM OrderDate) as MonthNum,
> Sum(ItemTotal)
> from Orders
> left join Itemlist on (ItemList.OrderNo = Orders.OrderNo)
> Group By YearNum, MonthNum
> Order By YearNum, MonthNum
>
> I get various errors from 'Capability Not Supported' to 'Invalid use of
> Keyword ..' - All the errors seem to point to my Group By line.
>
> BTW - I've tried the Group By line with the following too ...
>
> Group By EXTRACT(YEAR FROM OrderDate), EXTRACT(Month FROM OrderDate)
>
> Do you have any other suggestions please ?
>
> Thank you again for your time.
>
> John.
>
>
> "pr" <pr@...> wrote in message
> news:eavq86$181g$1@......
>> "John Robson" <johnandamy@...> wrote in message
>> news:eavebr$12fr$1@......
>>> Hello all,
>>>
>>> I have two tables in Master/Detail fashion, 'Orders' and 'ItemList'. The
>>> orders table contains a date field 'OrderDate', and the ItemList table
>>> lists the items ordered for any particular OrderDate.
>>>
>>> I'd like a report that totals up all costs for a month and year (so I can
>>> chart the results). What I mean is, SUM the item costs for each month and
>>> year.
>>>
>>> So if I had the following data ....
>>>
>>> Order Date
>>> 01/03/2003
>>> Description: Cost:
>>> Item A: 1.00
>>> Item B: 1.15
>>> Item C: 2.00
>>>
>>> 23/03/2004
>>> Item A: 1.00
>>> Item B: 1.15
>>> Item C: 2.00
>>>
>>> 24/03/2004
>>> Item D: 7.00
>>> Item B: 1.15
>>> Item C: 2.00
>>>
>>>
>>> 17/12/2004
>>> Item D: 7.00
>>> Item B: 1.15
>>> Item C: 2.00
>>>
>>>
>>> I would like a report to list the output as follows :-
>>>
>>>
>>> March 2003 Total 4.15
>>> March 2004 Total 14.30
>>> December 2004 Total 10.15
>>>
>>> This is what I've been trying for my SQL statement ...
>>>
>>> Select OrderDate, SUM(ItemTotal)
>>> from orders
>>> left join ItemList on (ItemList.OrderNo = Orders.OrderNo)
>>> Group by OrderDate
>>> Order by Orderdate
>> Group by EXTRACT(YEAR FROM OrderDate), EXTRACT(MONTH FROM OrderDate)
>> Order by EXTRACT(YEAR FROM OrderDate), EXTRACT(MONTH FROM OrderDate)
>>
>> Peter
>> Technisoft
>>
>>
>
>