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.