Can you solve this puzzle?
Comments table:
| id | entry_id | name | comment |
| 1 | 10 | wyred | tra lala |
| 2 | 10 | bobby | boo hoohoo |
| 3 | 10 | teddy | lol |
| 4 | 12 | wyred | roflcopter |
| 5 | 13 | wyred | pong! |
| 6 | 10 | wyred | stfu! |
id = primary key, auto increment
entry_id = id of the blog entry
Target Result:
| Name | Comment | Total no. of Comments posted |
| wyred | tra lala | 4 |
| bobby | boo hoohoo | 1 |
| teddy | lol | 1 |
| wyred | stfu! | 4 |
Explanation:
Can you come up with an SQL statement that retrieves all the comments for the entry of entry_id=10?
For each user that was retrieved, also calculate the sum of comments he’s made in total.
All this to be done in 1 SQL statement.
hmmmm using group by and count?
heh, I tried that but it ended up counting the number of posts in that entry and not the whole table.