count if 1

[bouwob]

I have the following query

SELECT t.domain, t.status
FROM T1 t
WHERE t.ID='something'

which gives me a result set that looks somewhat like this

HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Failed
HOMEOFFICE Failed
HOMEOFFICE Failed

The problem is I need a count of the total per domain, the domain and a count of the total that succeeded.

SELECT distinct (t.Domain), count(t.Domain)
--, sum(if stat.LastStateName = 'succeeded',1,0)
from T1 t
WHERE t.ID='something'
group by t.Domain, t.status


what do I need for the commented out section to give me a result set like

HOMEOFFICE 10 7

This query may or may not return more than 1 row if more than 1 domain is available.

 

[AlexCuse]

look in BOL at CASE.

Something like this (not tested)

select t.Domain
, count(t.*) as TOTAL
, sum(case when t.status = 'succeeded' then 1 else 0 end) as SUCCEEDED
, sum(case when t.stats = 'failed' then 1 else 0 end) as FAILED
from T1 t
group by t.Domain

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[bouwob]

excellent

once again in 3 minutes you solved somthing I was working on for much longer.

You are a very valued member.