Counting dups

When I run this query:
SELECT orderid, count(*) AS 'DupCount'
FROM orders
GROUP BY orderid
HAVING count(*) > 1
I get this:
Orderid DupCount
-- --
1 2
2 5
3 2
How can I return the count of dup records ( in this case 3) and the total
number of dups (in this case 9)?
Can I use ROLLUP with HAVING?SELECT COUNT(*), SUM(dupcount)
FROM
(SELECT orderid, COUNT(*) AS dupcount
FROM orders
GROUP BY orderid
HAVING COUNT(*) > 1) AS T
--
David Portas
SQL Server MVP
--|||Thanks David
Do you know if it is possible to use WITH ROLLUP with a HAVING clause. To
me it appears that the ROLLUP will return results independant of the HAVING.
"Dave" <david.frickNOtoSPAM@.homestore.com> wrote in message
news:unkRiNPoEHA.3876@.TK2MSFTNGP15.phx.gbl...
> When I run this query:
> SELECT orderid, count(*) AS 'DupCount'
> FROM orders
> GROUP BY orderid
> HAVING count(*) > 1
> I get this:
> Orderid DupCount
> -- --
> 1 2
> 2 5
> 3 2
> How can I return the count of dup records ( in this case 3) and the total
> number of dups (in this case 9)?
> Can I use ROLLUP with HAVING?
>
>|||Yes you can use CUBE/ROLLUP and HAVING together. The GROUPING() function is
useful in conjuction with ROLLUP and HAVING to filter the required summary
rows from the query.
I don't think ROLLUP will help much with your query because I believe you'll
still need a subquery to do a "double aggregation".
--
David Portas
SQL Server MVP
--