CODEWITHSUNDEEP
mysql
Jason
Jason

Reputation: 1307

Getting the number of posts that a user has posted?

How can I get the number of posts that a user has posted using one MySQL query?

I can't really think of anything but this, but there is no aggregate function on the join. So I'm not sure how to proceed. I am positive that joins will not accomplish what I need.

select a1.username as Username
from `logs` as a1
left join `logs` as a2
on a1.username = a2.username

For example, my logs table is filled with information about posts people have made. I want to find how many posts each user has made, i.e.

Username, Posts
User1     100
User2     200

etc

EDIT: Sorry for not providing enough information.

I have a table called logs and it has two columns. One column is called username and another column is called msg. It basically holds information about posts that people have posted.

For example, let's say someone named Red posts Hello world. It will be saved to the table logs and a new row will be created. username will be Red, and msg will be Hello world

I basically want to get the number of messages that EVERY SINGLE user has posted by their username. I.e. here is an example of what I want

Username Posts
Red 1
Blue 10
Sally 30

Upvotes: 0

Views: 92

Answers (3)

Michael Petrotta
Michael Petrotta

Reputation: 60902

I'm assuming that when you say you "can't use count(*) in a join", you mean that you tried and saw that it didn't work, rather than you can't use COUNT at all. So I'm using it here.

You're right that a JOIN is the wrong place for a COUNT. You want it up in the SELECT column list, and a GROUP BY down below. Aggregate by Username, and count the number of entries in each aggregate.

SELECT Username, COUNT(*) AS Count
FROM logs
GROUP BY Username

Upvotes: 1

Zero5msah
Zero5msah

Reputation: 21

try this

SELECT Username, count(Posts) 
FROM `logs`
GROUP BY Username;

Good luck.

Upvotes: 2

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

This query may help you, change this query as for your requirement 

SELECT users.*, count( posts.user_id ) 
FROM posts LEFT JOIN users ON users.id=posts.user_id 
GROUP BY posts.user_id

may be like 

SELECT users.username, count( logs.username ) as posts 
FROM users LEFT JOIN logs ON users.username=posts.username 
GROUP BY users.username

Upvotes: 0

Related Questions