Finding the average...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Finding the average...

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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:
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

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