Unique and Duplicate Records Count 3

[emasik]

I need to return to values (@unique and @dupes).

I start with:

declare @unique int,
@dupes int

Select name, count (1) from tblTest
Group by name
Having count (1) > 1

However, I need total unique and total dupes from field 'name'.

Any advice is welcome.

 

[SQLDenis]

select case count(*) when 1 then 'unique' else 'not unique' end,count(*) ,name
from tblTest
group by name

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Denis, that gives you one row per name.

select
   uniquecount = sum(case when cnt = 1 then 1 else 0 end),
   nonuniquecount = sum(case when cnt > 1 then 1 else 0 end)
from (
   select name, cnt = count(*) from tblTest group by name
) x

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[emasik]

Question for ESquared:

What x is for by the end of yur statment?

 

[ca8msm]

It's an alias for the derived table.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]