基于其他列的 SUM(DISTINCT)

2022-01-09 00:00:00 math sum distinct mysql

我目前有一个看起来像这样的表:

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

相关文章