Hupp. Wrong group. Sorry!

/D

D Marmur wrote:

> 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
>>>
>>>
>>
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
> keyword-searchable FAQ, community code contributions and more !
> Yahoo! Groups Links
>
>
>
>
>
>
>