Tonja - 1 year ago 69
MySQL Question

mysql: count number of values in each group

I have a query:

SELECT
count(session_id_open) as opens,
count(session_id_visit) as visits,
date_intervals_open,
group_concat(date_intervals_visit)
FROM
bla
GROUP BY date_intervals_open

I get following table. What I need is to find percentage wise the occurrences of each value appearing in group_concat. So, basically, I need to count number of values (date_intervals_visit) in each group (data_intervals_open)

opens visits date_intervals_open group_concat
213 5 day (12-16) evening (17-21),evening (17-21),day (12-16),day (12-16),day (12-16)
113 0 evening (17-21) NULL
11 0 late evening (22-00) NULL
396 12 morning (5-11) morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11)
9 0 night (1-4) NULL

That is approximately the table that I need to get. in First record evening has 40 because "evening (17-21)" appears twice and the number of all occurrences is 5. 2/5*100=40

opens visits date_intervals_open evening(17-21) day(12-16) morning (5-11)
213 5 day (12-16) 40 60 0
113 0 evening (17-21) NULL NULL NULL
11 0 late evening (22-00) NULL NULL NULL
396 12 morning (5-11) 0 0 100
9 0 night (1-4) NULL

PS: I used group_concat just to visualize the value that I have there. I do not have to use it as it will be an additional effort to parse it afterwards.

You essentially need a pivot, with some further calculations. The basis of my answer comes from the following excellent SO topic on pivoting records in MySQL. I assume that you have a fixed number of date_intervals_visit values, as these values seem to cover the entire day, therefore I use the conditional count approach with fixed number of counts. I'll add 2 categories in the sample code, you can extend it to cover all date_intervals_visit values.

SELECT
count(session_id_open) as opens,
count(session_id_visit) as visits,
date_intervals_open,
round(sum(if(date_intervals_visit='morning (5-11)',1,0)) / count(session_id_visit) * 100,2) as `morning (5-11)`,
round(sum(if(date_intervals_visit='day (12-16)',1,0)) / count(session_id_visit) * 100,2) as `day (12-16)`
FROM
bla
GROUP BY date_intervals_open

If it is possible to have 0 visits by date_intervals_open value, then you need to check for 0 in the expression:

if(count(session_id_visit)=0, 0, <above formula>)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download