MySQL SUM(DISTINCT) 基于其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11782292/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUM(DISTINCT) Based on Other Columns
提问by David
I currently have a table that looks something like this:
我目前有一个看起来像这样的表：
+------+-------+------------+------------+
| id | rate | first_name | last_name |
+------+-------+------------+------------+
What I need to do is get the SUMof 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 SUMof those rows to be 8, not 24, so it counts the rate once for each group of names.
我需要做的是获取SUMrate 列的 ，但每个名称只获取一次。例如，我有三行姓名 John Doe，每行的比率为 8。我需要SUM这些行的值为 8，而不是 24，因此它对每组名称计算一次比率。
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.
SUM(DISTINCT last_name, first_name)当然，这行不通，因为我试图对费率列求和，而不是对名称求和。我知道在计算单个记录时，我可以使用COUNT(DISTINCT last_name, first_name)，这就是我试图从中获得的行为类型SUM。
How can I get just SUMone rate for each name?
我怎样才能SUM为每个名字只获得一种费率？
Thanks in advance!
提前致谢！
采纳答案by Gonzalo.-
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..
如果你想得到那些小“ sums”的所有总和，你会得到所有表的总和..
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.
但是，如果由于某种原因是不同的（where例如，如果您使用 a ）并且您需要上面那个选择的总和，那就去做。
select sum(SumGrouped) from
( select sum (rate) as 'SumGrouped'
from yourTable
group by first_name, last_name) T1
回答by Georgy Vladimirov
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 BYin the inner query, I think you have to use aggregate functions in your SELECT. So, I think the answer is more like:
但是当你GROUP BY在内部查询中执行时，我认为你必须在你的SELECT. 所以，我认为答案更像是：
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.
我MAX()选择只为“last_name, first_name”组合选择一个“rate”，但MIN()应该工作相同，假设“last_name, first_name”总是引导我们到相同的“rate”，即使它在表中多次出现。这似乎是 David 最初的假设——对于唯一的名称，我们只想获取一次费率，因为我们知道它将是相同的。
回答by Paul
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):
首先，您可以通过散列将字符串转换为数字。下面的 SQL 对名字和姓氏进行 MD5 哈希，返回 32 个十六进制数字。SUBSTRING 取其中的前 8 个，CONV 将其转换为 10 位数字（理论上这可能不是唯一的）：
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.
然后将其除以一个非常大的数字并将其添加到比率中。你最终会得到一个像 8.0000019351087950 这样的比率。您必须使用 FORMAT 来避免 MySQL 截断小数位。现在，此比率对于每个名字和姓氏都是唯一的。
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:
然后，如果你对它做 SUM DISTINCT，它只会计算 8 一次。然后你需要对结果进行 FLOOR 去除多余的小数位：
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.
我在做一个更复杂的查询时发现了这种方法，该查询连接并分组了几个表。我仍然不确定我是否会使用它，因为它非常可怕，但它确实有效。对于回答这个问题的人来说，这也太晚了 6 年。
回答by Vasil Nikolov
SELECT SUM(rate)
FROM [TABLE]
GROUP BY first_name, last_name;
回答by MetalFrog
SELECT SUM(rate)
FROM [TABLE]
GROUP BY CONCAT_WS(' ', first_name, last_name);
回答by Manisha Mahawar
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.htmllink for further reading.
您可以使用上面提供的任何代码示例，因为没有任何聚合函数的 group by 子句将为每个分组条件返回一个不确定的记录。您可以参考http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html链接以进一步阅读。
