Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
    
Which RDBMS????? –  FreshPhilOfSO Mar 21 '13 at 11:20
    
MySQL, I updated the tags. –  powtac Mar 21 '13 at 11:51
add comment

2 Answers

up vote 2 down vote accepted

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 ;
share|improve this answer
    
Works perfectly! –  powtac Mar 21 '13 at 12:43
add comment

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
1  
I could not agree more with @ypercube. –  KookieMonster Mar 21 '13 at 12:58
add comment

Your Answer

 
discard

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.