Analysing the activity of a User-CodePudding

I have 2 tables:

Users (user_id, plan_type)

Hours_DATA (User_id, subplan, hours)

The column subplan has 3 types ('A','B','C').

I am trying to fetch user details along with their hours analysis, i.e, if the user_hours>10 across all subplans then he is a 'high' user and user_hours>5 but <10 as 'average' and <5 as 'low'.

Sample Tables:

Users: 
user_id  plan_type
9679     Life

Hours_DATA:
user_id  subplan_type  hours
9679       A            4
9679       B            7
9679       C            3

OUTPUT:
user_id  plan_type  A  B  C  profile
9679      Life      4  7  3  high

Note: Each plan has the 3 sub_plans.

I am not able to understand how I can get my subplan_types as columns.

I am stuck here:

SELECT (CASE WHEN SUBPLAN_TYPE ='A' THEN HOURS END) A,
(CASE WHEN SUBPLAN_TYPE ='A' THEN HOURS END) A,
(CASE WHEN SUBPLAN_TYPE ='A' THEN HOURS END) A
FROM HOURS_DATA

I am using MySQL for the same.

CodePudding user response：

You can use a conditional aggregation

SELECT user_id, 
   sum((CASE WHEN SUBPLAN_TYPE ='A' THEN HOURS END)) A,
   sum((CASE WHEN SUBPLAN_TYPE ='B' THEN HOURS END)) B,
   sum((CASE WHEN SUBPLAN_TYPE ='C' THEN HOURS END)) C
FROM HOURS_DATA
GROUP BY user_id

CodePudding user response：

The obvious format for this is one record per subplan type, as your data is already laid out that way. But, if you know there will always be exactly three (or possibly at most three) subplans, you can flatten the data structure by joining to each subplan explicitly:

select user_id, plan_type, A.hours as A, B.hours as B, C.Hours as C
from users
join hours_data A on A.user_id = users.user_id and A.subplan_type = "A"
join hours_data B on B.user_id = users.user_id and B.subplan_type = "B"
join hours_data C on C.user_id = users.user_id and C.subplan_type = "C"

(make them all left joins if the different subplans may not be present.)