MySQL Count 2 values from same field -

MySQL Count 2 values from same field -

how can count 2 different values same field? have field called jobs , i'd count how many doctors , how many policemen in same query.

this query counts both returns them in same result. i'd separate result. here query:

use dbassignmentpartc; select cu_city city, count(cu_job) totalpolicedoctors tblcustomers cu_job = 'doctor' or cu_job = 'policeman' grouping cu_city order totalpolicedoctors desc;

is possible still count both doctor's , policemen , homecoming them in separate columns?

i have tried searching on here , on google first year pupil may searching/using wrong terms , apologize if has been answered , i've missed it.

thanks in advanced

just alter count(cu_job) sum(cu_job="doctor") , sum(cu_job ="policeman")

select cu_city city, sum(cu_job = "doctor") totaldoctors , sum(cu_job = "policeman") totalpolice, sum(case when cu_job = "doctor" or cu_job = "policeman" 1 else 0 end) totalpolicedoctors tblcustomers grouping cu_city order totalpolicedoctors desc;

this returns city count of each doc , count of each police officer total count

fiddle demo

note: know how working (so can explain professor incase need to) sum(cu_job = "doctor") returns boolean value meaning each row cu_job = "doctor" either true or false. mysql (as other programming languages) treats true 1 , false 0. summing 'true' values give count of specific value within column.

mysql count