基于其他列的 SUM(DISTINCT)
我目前有一个看起来像这样的表:
I currently have a table that looks something like this:
+------+-------+------------+------------+
| id | rate | first_name | last_name |
+------+-------+------------+------------+
我需要做的是获取 rate 列的 SUM，但每个名称只有一次.例如，我有三行姓名 John Doe，每行的比率为 8.我需要这些行的 SUM 为 8，而不是 24，因此它为每组名称计算一次比率.
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) 当然是行不通的，因为我试图对比率列求和，而不是对名称求和.我知道在计算单个记录时，我可以使用 COUNT(DISTINCT last_name, first_name)，这就是我试图从 SUM 获得的行为类型.
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 一个费率?
How can I get just SUM one rate for each name?
提前致谢！
推荐答案
select sum (rate)
from yourTable
group by first_name, last_name
编辑
如果你想得到那些小sums"的所有总和，你会得到所有表的总和..
If you want to get all sum of those little "sums", you will get a sum of all table..
Select sum(rate) from YourTable
但是，如果由于某种原因不同(例如，如果您使用 where)并且您需要为上面的选择加上一个总和，就这样做.
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
相关文章