MySQL split up groupBy clause over multiple columns

Question

My knowledge of complex MySQL queries is not good and I'm having trouble searching or even phrasing this question.

I have a table that looks as follows.

I have a query that counts all the rows where 'type = student' and then groups those by year and month for which I use the created_column. This is pretty straightforward.

Query looks like this:

SELECT YEAR(created_at) as year, MONTH(created_at) as month, COUNT(type) as student_count
FROM users
WHERE type = "student"
GROUP BY year, month
ORDER BY year, month

I now need to add 2 more columns, 'subscription' & 'pay_per_video' which will further divide the total count for each month and year into student level type either 'subscription' OR 'pay_per_video'. Eg. if in Jan 2017 a total of 20 students registered. 12 might be per_per_video students while 8 in that month were subscription students. Any pointers are greatly appreciated.

UPDATE What I'm looking for is something like this: Table: Subject_Selection

Year    Month       Student Count     Pay Per View     Subscription
--------------------------------------------------------------------
2016     12              20                 20               0
2017     1               23                 12              11
2017     2               30                 10              20
2017     3                2                  1               1
2017     4               12                  2              10
2017     5               90                 40              50
2017     6               12                  0              12

Hi I would advise normalizing (to much informations are repeating) the database first to make it easy for you to do some simple query. Also for the count part I would advise using Procedure/Trigger to count automaticaly for you everytime you have a new entry. — A.V, Sep 13 '18 at 07:02
I've made an update to the question as to what structure I'm looking for. — Marcus Christiansen, Sep 13 '18 at 07:17

Fahmi · Accepted Answer · 2018-09-13T07:20:14.883

2

Use conditional aggregation like below:

SELECT YEAR(created_at) as year, MONTH(created_at) as month, COUNT(type) as student_count,
count(case when student_level='subscription' then 1 end) as subscriptioncount,
count(case when student_level='pay_per_vedio' then 1 end) as pay_per_vediocount
FROM users
WHERE type = "student"
GROUP BY year, month
ORDER BY year, month

edited Sep 13 '18 at 07:20

answered Sep 13 '18 at 07:10

Fahmi

37,315
5
22
31

score 0 · Answer 2 · answered Sep 13 '18 at 07:10

Please find below query for your need. I hope it will execute as you wish.

SELECT YEAR(created_at) as year, MONTH(created_at) as month, student_level, 
COUNT(type) as student_count
FROM users
WHERE type = "student"
GROUP BY year, month, student_level
ORDER BY year, month, student_level

score 0 · Answer 3 · answered Sep 13 '18 at 07:14

I now need to add 2 more columns, 'subscription' & 'pay_per_video' which will further divide the total count for each month and year into student level type either 'subscription' OR 'pay_per_video'.

Here I will suggest you to just add a single column named something like level_type, that will store either subscription OR 'pay_per_video' as value.

by doing this your SQL will be optimized to,

SELECT year, month, COUNT(student_count), level_type FROM (SELECT YEAR(created_at) as year, MONTH(created_at) as month, COUNT(type) as student_count
FROM users
WHERE type = "student"
GROUP BY year, month
ORDER BY year, month) as s
GROUP BY level_type

MySQL split up groupBy clause over multiple columns

3 Answers3