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 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 2

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 3

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.

Answer 4

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

Answer 5

Recently, I came across a similar problem, but with the exception that I already had a GROUP BY clause for a different purpose. Here is an example:

SELECT r.name, SUM(r.rate), MIN(e.created_at)
FROM Rates r LEFT JOIN Events e ON r.id = e.rate_id
GROUP BY r.id

The problem here is that because of JOIN with Event SUM(r.rate) would sum duplicates for entries with multiple Events. In my case the query was a lot more complicated, so I wanted to avoid having extra subqueries. Luckily, there is an elegant solution:

SELECT r.name, SUM(r.rate) / GREATEST(COUNT(DISTINCT e.event_id), 1), MIN(e.created_at)
FROM Rates r LEFT JOIN Events e ON r.id = e.rate_id
GROUP BY r.id

GREATEST function is used to prevent division by zero for entries without any Events. If you are summing integers, you also might want to CAST the sums to INT

Answer 6

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

Answer 7

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.

Answer 8

I found this thread looking for a better way to my solution, but i still didn't find a better one:

SELECT SUM(rate) FROM (SELECT DISTINCT rate, first_name, last_name) Q