SUM(DISTINCT) Based on Other Columns

Question

I currently have a table that looks something like this:

+------+-------+------------+------------+
| id   | rate  | first_name | last_name  |
+------+-------+------------+------------+

What I need to do is get the SUM of the rate column, but only once for each name. For example, I have three rows of name John Doe, each with rate 8. I need the SUM of those rows to be 8, not 24, so it counts the rate once for each group of names.

SUM(DISTINCT last_name, first_name) would not work, of course, because I'm trying to sum the rate column, not the names. I know when counting individual records, I can use COUNT(DISTINCT last_name, first_name), and that is the type of behavior I am trying to get from SUM.

How can I get just SUM one rate for each name?

Thanks in advance!

Answer 1

SELECT SUM(rate)
FROM [TABLE] 
GROUP BY first_name, last_name;

Answer 2

SELECT SUM(rate)
FROM [TABLE] 
GROUP BY CONCAT_WS(' ', first_name, last_name);

Answer 3

David said he found his answer as such:

SELECT SUM(rate) FROM (SELECT * FROM records GROUP BY last_name, first_name) T1

But when you do the GROUP BY in the inner query, I think you have to use aggregate functions in your SELECT. So, I think the answer is more like:

SELECT SUM(rate) FROM (SELECT MAX(rate) AS rate FROM records GROUP BY last_name, first_name) T1

I picked MAX() to pick only one "rate" for a "last_name, first_name" combination but MIN() should work the same, assuming that the "last_name, first_name" always leads us to the same "rate" even when it happens multiple times in the table. This seems to be David's original assumption - that for a unique name we want to grab the rate only once because we know it will be the same.

Answer 4

select sum (rate)
from yourTable
group by first_name, last_name

Edit

If you want to get all sum of those little "sums", you will get a sum of all table..

Select sum(rate) from YourTable

but, if for some reason are differents (if you use a where, for example) and you need a sum for that select above, just do.

select sum(SumGrouped) from 
(    select sum (rate) as 'SumGrouped'
    from yourTable
    group by first_name, last_name) T1

Answer 5

You can use any of the above code sample provided since with group by clause without any aggregate function will return an indeterminate one record for each grouping condition. You can refer http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html link for further reading.