Aymen Yasser Aymen Yasser - 6 months ago 10
MySQL Question

SQL select multiple coloumns each one count somthing

I have a problem when displaying several coloumns with counting, this is my table "Empo" :


idEmp DeptA DeptB
---- ---- ----
1 23 7
2 42 23
3 23 11
4 23 17


And I want to count number of idEmp , and the number of times where '23' is in every Dept to get something like this:


count(id) count(DeptA) count(DeptB)
---- ---- ----
4 3 1


also i have another table "Rapport"


idRap DeptA bonnus
---- ---- ----
1 23 200
2 42 23
3 23 346
4 77 44


and i want to get also the sum of the bonnus for the DeptA

How do I do this in MySQL?

thank you

Answer

The method I have used in the past is to use a combination of Count and Sum.

select count(idEmp), 
sum(Case when DeptA = 23 Then 1 else 0 End), 
sum(Case when DeptB = 23 Then 1 else 0 End)
from tableX

Edit for question. I would use a subselect for the new case to prevent duplicates being added to the original counts. see below.

select count(idEmp) as RecordCount, 
sum(Case when DeptA = 23 Then 1 else 0 End) as DeptA23Count, 
(select sum(bonnus) from Rapport where DeptA = 23) as BonnusForDeptA23,
sum(Case when DeptB = 23 Then 1 else 0 End) as DeptB23Count,
from tableX

Or something like that depending on where criteria.

Comments