Find the average number of n:m connections of two tables?

Question

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

User
====
id
name


Lists_Users
===========
user_id
list_id


List
====
id
title

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

Answer 1

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

SELECT 
    (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:

SELECT 
    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
FROM
    ( SELECT COUNT(*) AS total_assignments,
             COUNT(DISTINCT user_id) AS active_users
      FROM Lists_Users
    ) AS lu
  CROSS JOIN
    ( SELECT COUNT(*) AS total_users 
      FROM `User`
    ) AS u ;

Answer 2

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...