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

user19365190
October 17, 2022
70 views
0 votes
2 Answers

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

Tags: mysql sql

Answers

- NikhilS
- October 17, 2022 at 9:42 am
- 0 votes
0
I guess this should be it :
```
select sum(column1)/(select count(*) from  photos) from photos ;
```
Login or Signup to reply.

- TimBiegeleisen
- October 17, 2022 at 9:43 am
- 0 votes
0
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;
```
Login or Signup to reply.