StringForever StringForever - 5 months ago 15
MySQL Question

SQL Selection that gets the sum from specific cells

There is a table 'comments' with the columns 'writer' and 'likes'.
'writer' is the username of the writer and 'likes' the number of likes on that comment.

I need a selection now that selects the total amount of likes for a specific user.

For example this table:

writer likes
x 5
x 2
x 7
y 3


The selection (which I need) for user 'x' should return 14, for user 'y' 3.

Answer

If you want to select for a specific writer, you would use this SQL statement:

SELECT SUM(likes) FROM comments WHERE writer = 'x'

The SUM(likes) provides the "total" calculation, and the WHERE writer = 'x' indicates that this is for the specific writer 'x'.

To select the total number of likes for all users all at once, this will do it:

SELECT writer, SUM(likes) FROM comments GROUP BY writer

This includes both the writer and SUM(likes), and will group the totals by writer, using the GROUP BY writer clause.

If you'd like to select the total number of likes for some users (but not all of them) in one query, we can combine the previous statements, like this:

SELECT writer, SUM(likes) FROM comments WHERE writer IN ('x', 'y') GROUP BY writer

In this case the WHERE writer IN ('x', 'y') allows selection of results for only the writers 'x' and 'y'. This technique can be quite powerful for many situations in which selecting records for all writers would be overkill, and selecting writers one at a time might become a performance issue.