You got it! That's exactly what I want. Shame its not presently supported.
I'll try out the 'View' solution tomorrow - thanks. The only problem I
foresee is that my actual table has a date field and my inner query needs a
where clause to specify a user selectable date range.
Regards
Peter Gore.

-----Original Message-----
From: Alexander V.Nevsky [mailto:ded@...]
Sent: 21 April 2004 18:53
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Average Average!

--- In firebird-support@yahoogroups.com, "gorepj" <peter@g...> wrote:
> Hi all,
> here's my table
>
> MyTable
>
> Branch Clerk transaction item value
> 1 1 1 1 1.10
> 1 1 1 2 3.25
> 1 1 2 1 0.95
> 2 1 1 1 1.10
> 2 1 1 2 0.50
> 2 1 2 1 1.10
> 2 2 3 1 0.95
> 2 2 3 2 0.55
> 2 3 4 1 9.99
> etc...
> What I need to get is average transaction value by branch by clerk
> from a single query. So I need to sum values for each transaction
> by clerk by branch then average those summed values so I have what I
> need, average transaction value by clerk by branch. I've trie but no
> luck so far. Any ideas?

Can't say I clearly understand, but seems you want something like

Select Avg(SumV), Branch, Clerk
From
(Select Sum(Value) SumV, Branch, Clerk, Transaction
From MyTable
Group By Branch, Clerk, Transaction
)
Group By Branch, Clerk?

FB does'nt support this kind of queries, it is planned for 2.0
version. Now you can create view for inner select which calculate Sums
and perform on this view outer select which calculate Avg.

Best regards,
Alexander.




Yahoo! Groups Links