At 09:41 PM 14/02/2005 +0100, you wrote:

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

Why not just write a simple stored procedure that returns the result you
want, and save bandwidth and client processing cycles?
select sum("DuplicatesPerCategory") "Duplicates"
from (
select count(*) - 1 "DuplicatesPerCategory"
from "TheTable"
group by "Category"
having count(*) > 1
)

(double quotes omitted to clear fog)

create procedure count_us
returns (total_dups integer)
as
declare variable dup_count integer = 0;
declare variable cat varchar(10); /* or whatever it is */
begin
total_dups = 0;
for select
Category, /* <--- GROUP BY won't work without this! */
count(*)
from TheTable
group by Category
into :cat, :dup_count
do
begin
if (dup_count > 1) then
total_dups = total_dups + dup_count -1;
dup_count = 0;
end
suspend;
end

From the client:
select total_dups from count_us

./hb