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?

Can I get active record query for codeigniter? – Somnath Muluk Dec 02 '11 at 05:22 — Somnath Muluk, Dec 02 '11 at 05:22

Adam Wenger · Accepted Answer · 2011-11-30T06:32:57.233

7

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

edited Nov 30 '11 at 06:32

answered Nov 30 '11 at 05:44

Adam Wenger

17,100
6
52
63

1

Weneger: The last ELSE condition is empty, 0 should be placed there i guess. – Yaqub Ahmad Nov 30 '11 at 06:28
1

@yaqubAhmad Good catch, thank you. I added the 0 to the last `CASE` – Adam Wenger Nov 30 '11 at 06:34

score 1 · Answer 2 · answered Nov 30 '11 at 05:43

1

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'

answered Nov 30 '11 at 05:43

Zohaib

7,026
3
26
35

1

Have you seen this statement "I know it can get by 3 different queries" in the question? – Yaqub Ahmad Nov 30 '11 at 06:32

score 1 · Answer 3 · answered Nov 30 '11 at 06:23

1

This is the modified version of Adam Wenger 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

answered Nov 30 '11 at 06:23

Yaqub Ahmad

27,569
23
102
149

Sorry, can you elaborate on that? – Yaqub Ahmad Dec 02 '11 at 05:57
I got solution. You can check my solution. – Somnath Muluk Dec 02 '11 at 07:14

score 1 · Answer 4 · answered Nov 30 '11 at 06:51

1

I think this works fine:

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

answered Nov 30 '11 at 06:51

Carlos Jaime C. De Leon

2,476
2
37
53

1

`+` is not a concatenation operator in MySQL. `||` is, but in ANSI SQL mode only. – newtover Nov 30 '11 at 08:04

Somnath Muluk · Answer 5 · 2011-12-04T10:26:18.047

I got solution for active record query 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;

Counts on single column for different conditions

5 Answers5