0

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 NULL 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
Improve this question

2 Answers 2

Reset to default
1 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

on the SELECT..... FROM users query, mysql complains about a sytax error near CASE) I have COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END CASE) as count_A, COUNT(CASE WHEN engagement_item.type_code LIKE 'wm12%' THEN 1 ELSE NULL END CASE) as count_B, COUNT(CASE WHEN engagement_item.type_code LIKE 'wm6%' THEN 1 ELSE NULL END CASE) as count_C, AmI on the right track?
Use your pre-existing query on users and then join onto engagement instead and you should be able to use this query ...
Doesnt like the END CASE) but seems to do the job with just END) Its giving me very odd numbers though. Probably another issue somewhere. Thanks for the help though.
You may not need the CASE at the end of each END CASE ... I originally thought you did need it. So it should be as I have edited my post.
Define odd numbers ... higher than you expected? If yes, then you may have missed a join condition or caused an accidental 1 -> many relationship.
|
1

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.

Improve this answer

3 Comments

Ah, that's clever. I'll give that a go.
Hm. Both this and Judda response do the select from engagement, and join users, but I already have a quite complicated query selecting from users that I need to append this to.
@user277260: Then you just need to merge the GROUP BY and SUM/CASE (or COUNT/CASE if you like that better) stuff into your query. You didn't supply a query to start with so we both just whipped up a demo of the technique. The join order shouldn't affect the results with an inner join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.