CODEWITHSUNDEEP
sqlsql-servercount
Atreyu Tan
Atreyu Tan

Reputation: 1

Count specific values that belong to the same column SQL Server

I would like to ask for help I would like to count the number of records but having 2 conditions for 2 specific values only from a one single column:

Count the records first:

count(service) as contacts

service | state | address
service1| 1     |123
service2| 1     |321
service3| 3     |332
service1| 2     |333
service2| 2     |111
service3| 3     |333

1st result

Service | Contacts | status
service1| 1        | 1
service1| 1        | 2
service1| 1        | 3
service2| 1        | 1
service2| 1        | 2
service2| 1        | 3

if status = 1 and 2 then add to count else 0 (only count who's "status" is equal to 1 and 2.

Result:

Final result

Service  | Contacts 
Service1 | 2       
Service2 | 2

sorry for the confusion Thanks for your big help

Upvotes: 0

Views: 60

Answers (2)

JohnHC
JohnHC

Reputation: 11195

select service,
       sum(case when status in(1,2) then contacts else 0 end) as contact_count
from MyTable
group by service

What this will do is evaluate each row and include the contacts value in the sum where the status is one that is required. As it is an aggregate, you need to group by the service

Upvotes: 0

Mihai
Mihai

Reputation: 26784

This should work for all major databases

SELECT service,
SUM(CASE WHEN status IN(1,2) THEN contacts ELSE 0 END) as Contacts
FROM (your query) as x
GROUP BY service

Upvotes: 1

Related Questions