CODEWITHSUNDEEP
mysqlcount
Jamie Sutton
Jamie Sutton

Reputation: 163

MySQL Count 2 values from same field

How can I count two different values from the same field? I have a field called Jobs and I'd like to count how many Doctors and how many Policemen in the same query.

This is my query which counts both but returns them in the same result. I'd like to separate this result. Here is my query:

USE DBAssignmentPartC;
SELECT Cu_City as City, Count(Cu_Job) AS TotalPoliceDoctors FROM tblCustomers
WHERE Cu_Job = 'Doctor' OR Cu_Job = 'Policeman'
Group by Cu_City ORDER BY TotalPoliceDoctors DESC;

Is it possible to still count BOTH Doctor's and Policemen and return them in their separate columns?

I have tried searching on here and on Google but as a first year student I may be searching/using the wrong terms and I apologize if this has been answered and I've missed it.

Thanks in advanced

Upvotes: 2

Views: 1161

Answers (2)

Steve
Steve

Reputation: 11963

try this. You should be getting two rows for each city, one for policeman one for doctor with count

USE DBAssignmentPartC;
SELECT Cu_City as City, Count(Cu_Job) AS count FROM tblCustomers
WHERE Cu_Job = 'Doctor' OR Cu_Job = 'Policeman'
Group by Cu_City, Cu_Job ORDER BY count DESC;

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

Just change your Count(Cu_Job) to SUM(Cu_Job="Doctor") , SUM(cu_job ="Policeman")

SELECT 
    Cu_City as City, 
    SUM(Cu_Job = "Doctor") as totalDoctors , 
    SUM(Cu_Job = "Policeman") AS totalPolice, 
    SUM(CASE WHEN Cu_Job = "Doctor" OR Cu_Job = "Policeman" THEN 1 ELSE 0 END) AS TotalPoliceDoctors 
FROM tblCustomers
GROUP BY Cu_City 
ORDER BY TotalPoliceDoctors DESC;

this returns by city the count of each doctor and the count of each policeman as well as the total count

FIDDLE DEMO

NOTE: just so you know how this is working (so you can explain it to your professor incase you need to) SUM(Cu_Job = "Doctor") returns a boolean value meaning for each row Cu_Job = "Doctor" will either be true or false. MySQL (as well as basically all other programming languages) treats a true as 1 and a false as 0. so summing up the 'true' values will give you a COUNT of the specific value inside a column.

Upvotes: 4

Related Questions