SUM(DISTINCT) Based on Other Columns

user_1415625 0
Viewed 70267

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!

math distinct sum mysql
8 Answers

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.

user_43709990

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

user_75671090

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
user_96647330
Related