Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to know to how many lists a user is assigned in average.




Is there a good way of receiving this number via SQL?

share|improve this question
Which RDBMS????? – Phil Mar 21 '13 at 11:20
MySQL, I updated the tags. – powtac Mar 21 '13 at 11:51
up vote 2 down vote accepted

You only need the counts from the 2 tables and a division:

    (SELECT COUNT(*) FROM Lists_Users)  /  (SELECT COUNT(*) FROM `User`) 
    AS average_lists_per_user ;  

If you want more statistical information, you can write it using derived tables:

    total_users, active_users, total_assignments, 
    total_assignments / total_users                  -- this is
        AS average_lists_per_user,                   -- what you want
    total_assignments / active_users 
        AS average_lists_per_active_user
    ( SELECT COUNT(*) AS total_assignments,
             COUNT(DISTINCT user_id) AS active_users
      FROM Lists_Users
    ) AS lu
    ( SELECT COUNT(*) AS total_users 
      FROM `User`
    ) AS u ;
share|improve this answer
Works perfectly! – powtac Mar 21 '13 at 12:43

I have no idea which RDBMS you are using, but on SQL Server using CTEs (Common Table Expression) it might look like something similar:

with tbl_count (user_id, nboflists) as 
   select user_id, count(list_id)
   from lists_users
   group by user_id
select avg(nboflists)
from tbl_count

There must also be dozen of other (better?) ways of doing this...

share|improve this answer
Thanks you for the idea! – powtac Mar 21 '13 at 11:51
This is equivalent to average_lists_per_active_user in my answer. It will be probably be slower and you don't need it for average calculation. But if you want to calculate some other stats, like median or deviation, you'll need this (group by user_id) subquery. – ypercubeᵀᴹ Mar 21 '13 at 12:49
I could not agree more with @ypercube. – KookieMonster Mar 21 '13 at 12:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.