用于对重复项进行分组和计数的SQL语句

Question

I have a table called reports which looks a little like this:

|--|-------|------|-------------|-----------|------|
|ID|User_ID|Mod_ID|Mod_Timestamp|Mod_Comment|tstamp|
|--|-------|------|-------------|-----------|------|
|0 |   0   |   1  | 1387025342  |  XYZBLEH  |123123|
|--|-----------------------------------------------|

I also have another table called reports_users which looks like this:

|--|----|
|ID|Name|
|--|----|
|0 |Damo|
|--|----|

The user ID and Mod_Id from Report table both referance the ID from the Report_Users table, Now what i need is Sometimes inside the Mod_ID column there will be duplicates, What i would like to do is group these together and count them as sort of a tally of the duplicates so it looks a little something like this:

|--|----|-----|
|ID|Name|count|
|--|----|-----|
|0 |Damo|  16 |
|--|----|-----|

I can get the name to be added onto the table, but I cannot seem to group it or count them without causing problems, I've done this via a Inner Join statement but I cannot seem to get figure out how to do what I need. If anybody here could help with this I'd be most greatful.

Here is the SQL which I mentioned above

SELECT * FROM request as request_table INNER JOIN(SELECT name, id as uid from request_user)AS user ON(request_table.Mod_id = user.uid)

Regards, Damien

Answer 1

No need at all for a subselect:

SELECT reports.ID, reports_users.Name, count(reports.ID) AS count
FROM reports
LEFT JOIN reports_users ON reports.User_ID = reports.ID
GROUP BY reports.ID