Many forums and websites display user information on posts or in tables (when you are looking at more than 1 user)
For example you might have a forum thread and each post on it displays information about the the poster like this:
Name: John Email: email@email.com Posts: 450
They probably have a table for users which is where the name and email are stored, and then a table for posts.
The only think I can think of to do it in one sql statement would be to add 1 to a row (the one that corresponds to the user making the post) in the user table each time the user posted which seems like a bad solution to me.
How do they count the number of posts for each user?
They use the SQL COUNT() function to dynamically generate the count:
SELECT COUNT( *) FROM posts WHERE user_id = 1
This will count the number of rows in the posts table which were written by users who match the criteria user_id = 1.
To do this in one query, you can use a JOIN, and GROUP BY the identifier, like so:
SELECT username, user_email, COUNT( p.post_id) as num_posts
FROM users u
LEFT JOIN posts p
ON p.user_id = u.user_id
WHERE u.user_id = 1
GROUP BY u.user_id
Assuming that the email is unique in the user table, you can do:
SELECT u.name, u.email, count(*)
FROM users u, posts p
WHERE u.user_id = p.user_id
GROUP BY u.email;