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