Mysql COUNT result rows for a related table

I have

users
------------------------
id | name | other_stuff.....

.

engagement
------------------------
user_id | type_code |

type_code is a varchar, but either A, B, C or NULL

[ EDIT for clarity: Users can have many engagements of each type code. SO I want to count how many they have of each. ]

I want to return ALL user rows, but with a count of A, B and C type engagements. E.g.

users_result
------------------------
user_id | user_name | other_stuff..... | count_A | count_B | count_C |

I've done quite a bit of searching, but found the following issues with other solutions:

• The "other_stuff..." is actually grouped / concatenated results from a dozen other joins, so it's a bit of a monster already. So I need to be able to just add the additional fields to the pre-existing "SELECT ...... FROM users..." query.

• The three additional required bits of data all come from the same engagement table, each with their own condition. I havent found anything to allow me to use the three conditions on the same related table.

Thanks

[edit]

I tried to simplify the question so people didn't have to look through loads of unnecessary stuff, but seems I might not have given enough info. Here is 'most' of the original query. I've taken out a lot of the selected fields as there are loads, but I've left most of the joins in so you can see basically what is actually going on.

SELECT 
  user.id, 
  user.first_name, 
  user.second_name, 
  GROUP_CONCAT(DISTINCT illness.id ORDER BY illness.id SEPARATOR ',' ) AS reason_for_treatment, 
  IF(ww_id=1000003, 1,'') as user_refused_program, 
  Group_CONCAT(DISTINCT physical_activity.name SEPARATOR ', ') AS programme_options, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NUL开发者_开发百科L END) as count_C 
FROM `user` 
LEFT JOIN session AS session_induction ON (user.id = session_induction.user_id AND session_induction.session_type_id = 3) 
LEFT JOIN stats AS stats_induction ON session_induction.id = stats_induction.session_id 
LEFT JOIN session AS session_interim ON (user.id = session_interim.user_id AND session_interim.session_type_id = 4) 
LEFT JOIN stats AS stats_interim ON session_interim.id = stats_interim.session_id 
LEFT JOIN session AS session_final ON (user.id = session_final.user_id AND session_final.session_type_id = 5) 
LEFT JOIN stats AS stats_final ON session_final.id = stats_final.session_id 
LEFT JOIN user_has_illness ON user.ID = user_has_illness.user_id 
LEFT JOIN illness ON user_has_illness.illness_id = illness.id 
LEFT JOIN user_has_physical_activity ON user.ID = user_has_physical_activity.user_id 
LEFT JOIN physical_activity ON user_has_physical_activity.physical_activity_id = physical_activity.id 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
WHERE (user.INDUCTION_DATE>='2010-06-09' AND user.INDUCTION_DATE<='2011-06-09' AND user.archive!='1' ) 
GROUP BY user.id, engagement_item.user_id

It's worth mentioning that it works fine - returns all users with all details required. Except for the count_A B and C cols.

[edit added slightly more simplified query below]

Stripped out the unrelated joins and selects.

SELECT 
  user.id, 
  user.first_name, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B, 
  COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_C 
FROM `user` 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
GROUP BY user.id, engagement_item.user_id

---

SELECT e.user_id, u.name,
       COUNT(CASE type_code WHEN 'A' THEN 1 ELSE NULL END) as count_A,
       COUNT(CASE type_code WHEN 'B' THEN 1 ELSE NULL END) as count_B,
       COUNT(CASE type_code WHEN 'C' THEN 1 ELSE NULL END) as count_C
FROM engagement e join users u on (e.user_id = u.id)
GROUP BY e.user_id, u.name

I would use COUNT instead of SUM just because that is what it is made for, counting things when not NULL.

SELECT 
  user.id, 
  user.first_name, 
  user.second_name, 
  GROUP_CONCAT(DISTINCT illness.id ORDER BY illness.id SEPARATOR ',' ) AS reason_for_treatment, 
  IF(ww_id=1000003, 1,'') as user_refused_program, 
  Group_CONCAT(DISTINCT physical_activity.name SEPARATOR ', ') AS programme_options,
  ei.count_A, ei.count_B, ei.count_C
FROM `user` 
LEFT JOIN ( SELECT user_id
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_A
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END) as count_B 
    , COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END) as count_C 
    FROM engagement_item
    GROUP BY userid ) ei
LEFT JOIN session AS session_induction ON (user.id = session_induction.user_id AND session_induction.session_type_id = 3) 
LEFT JOIN stats AS stats_induction ON session_induction.id = stats_induction.session_id 
LEFT JOIN session AS session_interim ON (user.id = session_interim.user_id AND session_interim.session_type_id = 4) 
LEFT JOIN stats AS stats_interim ON session_interim.id = stats_interim.session_id 
LEFT JOIN session AS session_final ON (user.id = session_final.user_id AND session_final.session_type_id = 5) 
LEFT JOIN stats AS stats_final ON session_final.id = stats_final.session_id 
LEFT JOIN user_has_illness ON user.ID = user_has_illness.user_id 
LEFT JOIN illness ON user_has_illness.illness_id = illness.id 
LEFT JOIN user_has_physical_activity ON user.ID = user_has_physical_activity.user_id 
LEFT JOIN physical_activity ON user_has_physical_activity.physical_activity_id = physical_activity.id 
LEFT JOIN engagement_item ON user.ID = engagement_item.user_ID 
WHERE (user.INDUCTION_DATE>='2010-06-09' AND user.INDUCTION_DATE<='2011-06-09' AND user.archive!='1' ) 
GROUP BY user.id, engagement_item.user_id, ei.count_A, ei.count_B, ei.count_C

---

Something like this perhaps?

select e.user_id, u.name,
       sum(case e.type_code when 'A' then 1 else 0 end) as count_A,
       sum(case e.type_code when 'B' then 1 else 0 end) as count_B,
       sum(case e.type_code when 'C' then 1 else 0 end) as count_C
from engagement e join users u on (e.user_id = u.id)
group by e.user_id, u.name

The interesting part is the use of CASE inside the SUM to split the counting into three chunks.