Counts one column for different conditions

Question

I need 3 different counts for one column for different conditions.

Table structure:

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

experience has 3 different meanings - 1) positive 2) negative 3) neutral

I need 3 different counts "count_positive", "count_negative" and "count_neutral", where the condition is c_id = 10.

I know that he can receive three different requests. Can I get 3 bills for one request?

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' 

Answer 3

This is a modified version of Adam Wenger's answer:

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

Answer 4

I think this works great:

 select 'count of ' + experience, count(experience) from interview where c_id=10 group by experience 

Answer 5

I got a solution for an active write request in Codeigniter:

 $this->db->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'); $this->db->where('c_id',10); $query=$this->db->get('interview'); $result=$query->result(); $interview_experience=$result[0]; $positive_count=$interview_experience->CountPositive; $negative_count=$interview_experience->CountNegative; $neutral_count=$interview_experience->CountNeutral;