Subject Two-level group by?
Author Kjell Rilbe

I have two tables Master and Detail in a 1-M relationship. I want to
calculate the number of Masters for each count of Details, i.e. I want
to list how many Masters have Details, how many Masters have 1 Detail,
how many Masters hav 2 Details, etc.

Can this be done in FB 1.5 without creating a view or a stored procedure?

In a DB that supports "temporary tables" I'd do it like this:

select DetailCount, count(*) MasterCount
from (
select Master.Id MasterId, count(*) DetailCount
from Master
inner join Detail on Detail.MasterId = Master.Id
group by Master.Id
) Subquery
group by DetailCount

Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64