Multiple counts but by distinct name

Question

Post reply

Multiple counts but by distinct name

bmwiest

Newbie

Points: 2
More actions
February 3, 2010 at 10:02 am

#215225

I have a table like so:
Name | ServerOS | Client | Domain
ServerA, Win2k3, 1, NA
ServerB, Win2k3, 1, EU
ServerC, Win2k3, 1, SA
ServerD, Win2k, 0, NA
ServerA, Win2k3, 1, NA
I need a query that outputs the following:
Domain | Count of Client =1 | Count of Client = 0
But I need it to determine distinct names.
Any Ideas?
I have tried multiple count statements
select distinct domain
(select count(*) from table where os LIKE 'Win%' AND Client = 1) as 'Clients'
(select count(*) from table where os LIKE 'Win%' AND Client = 0) as 'No Clients'
from table
group by domain
but this did not result the correct information.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bmwiest Newbie Points: 2 More actions · Answer 1

I don't know if it helps but here is the actual code I was trying to use

Select Resource_Domain_OR_Workgr0,

(Select count (distinct Name0) WHERE (Operating_System_Name_and0 LIKE '%Server%' AND Client0 = 1)) as 'Agents',

(Select count (distinct Name0) WHERE (Operating_System_Name_and0 LIKE '%Server%' AND Client0 = 0)) as 'No Agents'

from v_r_system

Group By Resource_Domain_OR_Workgr0

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

How about this:

Select

domain,

Sum(Case when client = 1 then 1 else 0) as clients,

Sum(Case when client = o then 1 else 0) as no_clients

From

table

Group By

domain

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Jack Corbett (2/4/2010)
How about this:
Select
domain,
Sum(Case when client = 1 then 1 else 0) as clients,
Sum(Case when client = o then 1 else 0) as no_clients
From
table
Group By
domain

It looks like you have

Sum(Case when client = o then 1 else 0) as no_clients

and meant

Sum(Case when client = 0 then 1 else 0) as no_clients

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

CirquedeSQLeil (2/4/2010)
Jack Corbett (2/4/2010)
How about this:
Select
domain,
Sum(Case when client = 1 then 1 else 0) as clients,
Sum(Case when client = o then 1 else 0) as no_clients
From
table
Group By
domain
It looks like you have
Sum(Case when client = o then 1 else 0) as no_clients
and meant
Sum(Case when client = 0 then 1 else 0) as no_clients

Oops, that's what happens when you haven't finished your coffee yet. 😛

Unless here is a column o that will throw an error and hopefully if the OP didn't already catch the typo then they'll be able to fix it when the error hits.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

We'll see 😀

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events