题解 | #分组计算练习题#

select gender, university, count(*), avg(active_days_within_30), avg(question_cnt)
from user_profile
group by  gender, university

group by university，gender 反过来也可以

但是如下情况不行，会报错：不满足函数依赖

select gender, university, count(*), avg(active_days_within_30), avg(question_cnt)
from user_profile
group by gender

select gender, university, count(*), avg(active_days_within_30), avg(question_cnt)
from user_profile
group by university

报错分别是：

SQL_ERROR_INFO: "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_profile.university' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

SQL_ERROR_INFO: "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_profile.gender' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

究其原因，比如只group by gender

那么university是有很多个的，不能select显示出来，但是语句中select gender, university,所以报错

select gender, ....， group by gender就不会报错了，虽然不是正确答案，如下所示

select gender, count(*), avg(active_days_within_30), avg(question_cnt)
from user_profile
group by   gender