Counts on single column for different conditions

Question

I require 3 different counts on single column for different conditions.

Table structure:

interview-
id-int(10)
c_id-int(10)
experience-varchar2(100)

experience have 3 different values- 1)positive 2)negative 3)neutral

I require 3 different counts of "count_positive", "count_negative" and "count_neutral" for where condition of c_id=10.

I know it can get by 3 different queries. Can I able to get 3 counts by single query?

Answer 1

SELECT
   SUM(CASE experience
          WHEN 'positive' THEN 1
          ELSE 0
       END) AS CountPositive
   , SUM(CASE experience
            WHEN 'negative' THEN 1
            ELSE 0
         END) AS CountNegative
   , SUM(CASE experience
            WHEN 'neutral' THEN 1
            ELSE 0
         END) AS CountNeutral
FROM Interview
WHERE c_id = 10

Answer 2

select 'Positive Count' , count(*)
from interview
where experience = 'positive'
UNION
select 'Negative Count' , count(*)
from interview
where experience = 'negative'
UNION
select 'Neutral' , count(*)
from interview
where experience = 'neutral'