> Hi,
>
> I need to sum count duplicates in a table when grouping by a certain
> column. In SQL Server I've solved it using a nested subquery like this:
>
> select sum("DuplicatesPerCategory") "Duplicates"
> from (
> select count(*) - 1 "DuplicatesPerCategory"
> from "TheTable"
> group by "Category"
> having count(*) > 1
> )
>
> In other words, if there are X records of category Y, I want to count
> X-1 for that category, since that is the number of duplicates (excluding
> the "original"). I then want to sum these counts.
>
> Firebird doesn't support subqueries like this (yet). How can I get the
> same result?
>
> (I could simply skip the outer sum query and loop through the results of
> the inner query in my application, but it's rather neat to get the sum
> straight from the query.)
>
> Thanks,
> Kjell

create a view with your subselect, then select over it with the count(*)
BTW - do you really need the mixed case object identifiers? what a pain
Alan