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

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

CodePudding user response：

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

CodePudding user response：

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".