Hmm, Kjell, you got my brain thinking.

What about something like

select distinct (select count(*) from detail d
where d.MasterID = m.Id) as DetailCount,
(select count(m2.id) from master m2
where (select count(*) from detail d2 where d2.MasterID = m.Id) =
(select count(*) from detail d3 where d3.MasterID = m2.Id)) as
MasterCount
from master m

I've never tried anything like it, haven't done any syntax checking
and expect it to be slow on large data volumes. My guess is that it
answers your question 'can it be done', but I'm not certain if doing
it this way is better than waiting for Firebird 2.0.

I'd be interested in knowing the performance of this query - if it is
as slow as I suspect (and of course, if it gets you the desired result).

Set

--- In firebird-support@yahoogroups.com, Kjell Rilbe wrote:
>
> Hi,
>
> 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
>
> Thanks,
> Kjell
> --
> --------------------------------------
> Kjell Rilbe
> Adressmarknaden AM AB
> E-post: kjell.rilbe@...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64