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
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64