基于其他列的 SUM(DISTINCT) [英] SUM(DISTINCT) Based on Other Columns

问题描述

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

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

这篇关于基于其他列的 SUM(DISTINCT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！