We help IT Professionals succeed at work.

getting count of column that is "yes", "null" or 'No"

Medium Priority
406 Views
Last Modified: 2012-05-12
I can not remember the count syntex for returning a count of a column based on a specific entery
I need a cout(col1 where = "yes")

using ms sql server 2008.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Select count(col1) as col1_count
from table
where col1 = "yes"
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT
    SUM(CASE WHEN col1 = 'Yes' THEN 1 ELSE 0 END) AS Yes_Count,
    SUM(CASE WHEN col1 = 'No' THEN 1 ELSE 0 END) AS No_Count,
    SUM(CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) AS NULL_Count
    --, ...
FROM tablename
WHERE ....

Author

Commented:
this is the actual query so I don't think it is as simple _ do I need to put in a subquery

Author

Commented:
SELECT
    cc_calloutlog.CLUtilityID,
    cc_calloutlog.cldistrict, cc_calloutlog.clempid,
    cc_calloutlog.clInfo1, cc_calloutlog.clInfo2, cc_calloutlog.clInfo3,
    cc_calloutlog.clInfo4, cc_calloutlog.clStartCallTime, cc_calloutlog.clAttempts,
    cc_calloutlog.clResultofLastAttempt, cc_calloutlog.clAccepted
from
    cc_calloutlog
WHERE
  cc_calloutlog.clStartCallTime between @starttime and @endtime
  and (clattempts > 0 or clchannelstatus <> 'Prospective') and clcncrewname not like '%test%'
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Refresh this q in your browser and look at my earlier comment :-) .

Explore More ContentExplore courses, solutions, and other research materials related to this topic.