CODEWITHSUNDEEP
sqlcount
John Fisher
John Fisher

Reputation: 13

Counting number of unique names

I'm trying to return a given number of server names and then a count of how many came back. For instance:

select servername, count(distinct servername) number
from TABLE
where [target] = 'blah'
group by servername

This returns the following:

servername      number
server1         1
server2         1

What I'm attempting to get is for the "number" column to read "2," because there are 2 distinct servernames. This should be incredibly simple, but I'm completely at a loss.

Upvotes: 1

Views: 405

Answers (2)

user330315
user330315

Reputation: 

select servername, 
       count(*) over (partition by servername) as number
from the_table
where [target] = 'blah'

Window functions (the over() part) are supported by all modern DBMS including SQL Server (which you seem to be using)

Upvotes: 0

Taryn
Taryn

Reputation: 247680

You can use a correlated subquery:

select distinct servername, 
  (select count(distinct servername) 
   from yourtable
   where target = 'blah') number
from yourtable
where target = 'blah'

See SQL Fiddle with Demo

Upvotes: 3

Related Questions