How to get average of a counted column in sql-CodePudding

select count(user_id) 
from photos 
group by user_id ;

4
3
1
2
1
1
12
5
1
4
8
2
1
4
5
2
1
2
1
1
3
5
4
4
5
1
3
5
5
1
1
8
10
2
1
2
4
5
5
3
1
1
5
1
6
5
1
2
2
2
9
4
11
3
2
1
2
3
2
1
3
2



select avg(count(user_id)) 
from photos 
group by user_id;

Error Code: 1111. Invalid use of group function

How to get average of this column

CodePudding user response：

Do you want an average over the entire aggregated table?

SELECT AVG(cnt) AS avg_cnt
FROM
(
    SELECT COUNT(user_id) AS cnt 
    FROM photos
    GROUP BY user_id
) t;

CodePudding user response：

I guess this should be it :

select sum(column1)/(select count(*) from  photos) from photos ;