MySQL将groupBy子句拆分为多个列

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

我对复杂的MySQL查询的了解并不好,我在搜索甚至填写这个问题时遇到了麻烦。

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.

我有一个查询计算'type = student'的所有行,然后按年份和月份对我使用created_column进行分组。这非常简单。

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.

我现在需要添加2个列,'subscription'和'pay_per_video',这将进一步将每个月和每年的总计数划分为学生级别类型'订阅'或'pay_per_video'。例如。如果在2017年1月共有20名学生注册。 12可能是per_per_video学生,而当月8名是订阅学生。任何指针都非常感谢。

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

更新我正在寻找的是这样的:表: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

3 个解决方案

#1

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

#2

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

#3

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'.

我现在需要添加2个列,'subscription'和'pay_per_video',这将进一步将每个月和每年的总计数划分为学生级别类型'订阅'或'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.

在这里,我建议您只添加一个名为level_type的列,它将存储订阅或'pay_per_video'作为值。

by doing this your SQL will be optimized to,

通过这样做,您的SQL将被优化为,

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

#1