Ok I have this, lets say i have a sql table about articles that users send... i want a code to count the articles that each user send and return the username and the number of articles each user has send...
the result would be something like:
Users # Articles
1. Username1 - 23
2. Username2 - 21
3. Username3 - 17
4. Username4 - 10
i know has to do something with the COUNT(*) function of mysql but i had no luck
or maybe i am worng...
thanks
php and mysql Count(*)
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SELECT a.`Username`, COUNT( a.`Username` ) `num` FROM `table` a, `table2` b WHERE b.`author` = a.`Username` GROUP BY b.`author`extended that a little, because i always wanted to show messages without linked comments 
Code: Select all
SELECT
m.message_id AS message_id,
m.title AS title,
m.content AS content,
m.datetime AS datetime,
IF(ISNULL(comment_id), 0, COUNT(*)) AS count
FROM message AS m
LEFT JOIN comment AS c USING (message_id)
GROUP BY m.message_id
ORDER BY m.datetime";