Mysql – Find the average number of n:m connections of two tables

MySQLstatistics

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?

Best Answer

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 ;

Related Solutions

Mysql – Best way to set up database tables

You have no need to add a "modules" field to your main/users table. Just JOIN on the tables -- that's what Relational Databases are for. INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions. If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

SELECT * 
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins

Mysql – Group by reversible pair

You can use a UNION of two subqueries:

SELECT 
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id 
  UNION ALL
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id 
  ) AS tmp 
GROUP BY 
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.

Best Answer

Related Solutions

Mysql – Best way to set up database tables

Mysql – Group by reversible pair

Related Question