How to find AVG of Count in SQL-CodePudding

This is what I have

select avg(visit_count) from ( SELECT count(user_id) as visit_count from table )group by user_id;

But I get the below error

ERROR 1248 (42000): Every derived table must have its own alias

if I add alias then I get avg for only one user_id

What I want is the avg of visit_count for all user ids

Example 3,2.5,1.5

CodePudding user response：

It means that your subquery needs to have an alias.

Like this:

select avg(visit_count) from (
  select count(user_id) as visit_count from table
   group by user_id) a

CodePudding user response：

Your subquery is missing an alias. I think this is the version you want:

SELECT AVG(visit_count)
FROM
(
    SELECT COUNT(user_id) AS visit_count
    FROM yourTable
    GROUP BY user_id
) t;

Note that GROUP BY belongs inside the subquery, as you want to find counts for all users.