mysql 聚合函数 分租,如何在MySQL中组合聚合函数？

I'm just learning MySQL - is there a way to combine (or nest) aggregate functions?

Given a query:

SELECT user, count(answer) FROM surveyValues WHERE study='a1' GROUP BY user;

This will give me the number of questions answered by each user. What I really want is the average number of questions answered per user...something like:

SELECT avg(count(answer)) FROM surveyValues WHERE study='a1';

What's the correct way to compute this statistic?

If this is possible, is there a way to then break this statistic down for each question? (users can answer the same question multiple times). Something like:

SELECT avg(count(answer)) FROM surveyValues WHERE study='a1' GROUP BY question;

解决方案

You have to use subqueries:

SELECT x.user,

AVG(x.cnt)

FROM (SELECT user, COUNT(answer) AS cnt

FROM surveyValues

WHERE study='a1'

GROUP BY user) x

GROUP BY x.user

You can't wrap an aggregate with another aggregate. You could wrap an analytic in an aggregate, if MySQL supported analytic/ranking/windowing functions...