Page 1 of 1

Finding the average...

Posted: Mon Jan 08, 2007 12:14 pm
by seodevhead
Hey guys... I have a database application (mysql) that is very similar to these phpBB forums in that people submit posts to the application. I am trying to figure out what seemingly should be a very simple math problem, I am having trouble trying to figure out how to sculpt a MySQL query to get the number I am looking for.

I have thousands of users and each user has his or her own 'total post count', how would I go about determining what the average post count is for the average user?

Example:
The Database has a total of 4 users:

User 1 has a total of 2 posts.
User 2 has a total of 5 posts.
User 3 has a total of 8 posts.
User 4 has a total of 9 posts.

To find the average number of posts for the average user, I would take the four user's totals (2, 5, 8, 9), add them together... 24... then divide by the total number of users (4).. which leaves use with the average post count of '6'.

Now as for my database structure... I have a simple 'posts' table that allows the following records:

Code: Select all

CREATE TABLE posts (
      post_id INT NOT NULL AUTO_INCREMENT,
      user_id INT NOT NULL,
      post_text TEXT NOT NULL,
      date_added DATETIME NOT NULL,
      PRIMARY KEY (post_id)
);
Any idea how I should design this mysql query to produce this average post count for the avg user? I am really stumped on this one and would greatly appreciate any guidance or direction you guys could give me. Thanks for your help.

Posted: Mon Jan 08, 2007 12:16 pm
by volka

Posted: Mon Jan 08, 2007 12:28 pm
by seodevhead
Yea... I think the AVG() function would work well.. but I am not sure how to use it on this particular table since the total post counts are not in their own column. In other words... each post has its own record in the 'posts' table and I first need to count how many posts each user has, then with those 'each user' total post counts.. add them together and divide by the total users for the answer.

I am sure I could figure out a long winded process utilizing multiple queries and php mathematics.. but there has to be a relatively easy way to do this with mysql.

Do you understand what I am saying? :wink:

Posted: Mon Jan 08, 2007 12:45 pm
by GM
You may be missing something mathematically here...

Finding the total posts of each user, adding them together, and finding the average contains a redundant step...

Why do you need to find the total posts of each user?

Why not just take the count(*) from the posts table and divide it by the count(*) of the users table?

Posted: Mon Jan 08, 2007 1:20 pm
by seodevhead
GM wrote:Why do you need to find the total posts of each user? Why not just take the count(*) from the posts table and divide it by the count(*) of the users table?
Man, Don't I feel really stupid. I guess I would get the same answer if I just added up the number of total posts and divided by the number of users. Geez... that's too easy.

So now that we have the math much easier... how would I go about counting the number of unique users that have posts? Each user has multiple records in the 'posts' table... how could I use the COUNT() function to count how many unique users there are? Is there some kind of UNIQUE modifier? I am looking in MySQL docs but haven't found what I think may exist.

Thanks for your help.

Posted: Mon Jan 08, 2007 1:34 pm
by volka
try

Code: Select all

SELECT
      count(post_id)/count(distinct user_id)
FROM
    posts