SUM(DISTINCT) Based on Other Columns

select sum (rate)from yourTablegroup 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

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.

You can do this by making the values you are summing distinct. This is possible but is very very ugly.

First, you can turn a string into a number by taking a hash. The SQL below does an MD5 hash of the first and last name, which returns 32 hexadecimal digits. SUBSTRING takes the first 8 of these, and CONV turns that into a 10 digit number (it's theoretically possible this won't be unique):

CONV(SUBSTRING(MD5(CONCAT(first_name,last_name)), 1, 8), 16, 10)

Then you divide that by a very big number and add it to the rate. You'll end up with a rate like 8.0000019351087950. You have to use FORMAT to avoid MySQL truncating the decimal places. This rate will now be unique for each first name and last name.

FORMAT(rate + CONV(SUBSTRING(MD5(CONCAT(first_name,last_name)), 1, 8), 16, 10)/1000000000000000, 16)

And then if you do the SUM DISTINCT over that it will only count the 8 once. Then you need to FLOOR the result to get rid of the extra decimal places:

FLOOR(SUM(DISTINCT FORMAT(rate + CONV(SUBSTRING(MD5(CONCAT(first_name,last_name)), 1, 8), 16, 10)/1000000000000000, 16)))

I found this approach while doing a much more complicated query which joined and grouped several tables. I'm still not sure if I'll use it as it is pretty horrible, but it does work. It's also 6 years too late to be of any use to the person who answered the question.