CODEWITHSUNDEEP
mysqlsql
Shadywolf21
Shadywolf21

Reputation: 170

How to get the Count of a column which has different values in it

agent-name position qa_agent
First fatal admin
Second non fatal admin
Second non fatal admin

I need a output like this

agent-name Count of qa agent Count Fatal count Count Non fatal
First 1 1 0
Second 1 0 2

Upvotes: 3

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Fahmi's answer is fine. But MySQL allows a more concise version:

select agent_name,
       count(distinct qa_agent) as Count_qa_agent,
       sum(position = 'fatal') as Count_fatal,
       sum(position = 'non fatal') as Count_Non_fatal
from tablename
group by agent_name;

That is, you can add up boolean expressions without having to use explicit conditional logic, because 1 is equivalent to "true" and 0 is equivalent to "false".

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try using conditional aggregation

select agent_name,
       count(distinct qa_agent) as Count_qa_agent,
       count(case when position='fatal' then 1 end) as Count_fatal,
       count(case when position='non fatal' then 1 end) as Count_Non_fatal
from tablename
group by agent_name

Upvotes: 2

Related Questions