mysql average count from related table

3
votes

I have two tables:

users table

list of users

stories

list of stories - multiple stories per user

I want to know the average number of stories a user has. (not a specific user, for all users)

Expected results: 2.3 average stories per user

Tried:

select avg(w) from (select count(distinct user_id) as w from stories group by user_id) a; above ran but didn't seem correct

also:

SELECT user_id, ( SELECT COUNT(*) FROM stories w WHERE w.user_id = u.user_id ) as TotalStories FROM user u;

returned average stories for each user, not average for overall

2
mysqlsqlaggregate-functions
select avg(w) from (select count(distinct user_id) as w from stories group by user_id) a; – jhanifen
above ran but didn't seem correct – jhanifen
SELECT user_id, ( SELECT COUNT(*) FROM stories w WHERE w.user_id = u.user_id ) as TotalStories FROM user u; – jhanifen
put this in your question by editing your question so every member can have look into this. – rahularyansharma
above doesn't do an average just returns average of entire list of users – jhanifen

2 Answers

4
votes

First you need to know the number of stories per user:

select count(s.id) as n
from users u left outer join stories s on u.id = s.user_id
group by u.id

Then just apply avg to that:

select avg(n)
from (
    select count(s.id) as n
    from users u left outer join stories s on u.id = s.user_id
    group by u.id
) as dt
1
votes
SELECT COUNT(userid) AS totalusers, SUM(storycount) AS totalstories, SUM(storycount) / COUNT(userid) AS average_stories
FROM (
    SELECT users.id AS userid, COUNT(stories.id) AS storycount
    FROM users
    LEFT JOIN stories ON (users.id = stories.user_id)
    GROUP BY users.id
) AS child

Inner query does the per-user story counting. Outer query counts the total users, total stories, and the stories per user average.