在sql server 2008中使用distinct和sum

Question

我正在尝试获取每个帐户的SUM(Values)，但我的问题是尝试获取包含SUM(Values)的DISTINCT帐户的至少一行。

我有一些示例数据，例如：

Acct         Values    Name       Street     
123456789    100.20    John       66 Main Street
123456789    200.80    John       22 Main Avenue
222222222    50.25     Jane       1 Blvd
333333333    25.00     Joe        55 Test Ave
333333333    50.00     Joe        8 Douglas Road
555555555    75.00     Tim        12 Clark Ave
666666666    500.00    Tim        12 Clark Street
666666666    500.00    Tim        3 Main Rd.

我的问题包括：

SELECT DISTINCT Acct, SUM(Value) AS [TOTAL]
FROM TABLE_NAME
GROUP BY Acct

上面的查询使我接近所需的内容，但我需要整个行。

下面是我正在寻找的示例：

Acct         Total      Name       Addr1     
123456789    301.00     John       66 Main Street 
222222222    50.25      Jane       1 Blvd
333333333    75.00      Joe        55 Test Ave
555555555    75.00      Tim        12 Clark Ave
666666666    1000.00    Tim        12 Clark Street

谢谢。

Answer 1

如果您返回的地址无关紧要，那么您可以应用并聚合到其他列：

SELECT Acct, 
  SUM(Value) AS [TOTAL], 
  max(name) name, 
  max(Street) addr1
FROM TABLE_NAME
GROUP BY Acct;

请参阅SQL Fiddle with Demo

Answer 2

在大多数数据库中，您可以使用窗口函数(如row_number() )来完成此操作：

select acct, total, name, addr1
from (select t.*, row_number() over (partition by acct order by acct) as seqnum,
             sum(value) over (partition by acct) as Total
      from table_name
     ) t
where seqnum = 1;

Answer 3

我会使用窗口函数( OVER子句)来解决这个问题。

SELECT DISTINCT 
   Acct
  ,SUM([Values]) OVER (PARTITION BY Acct) AS 'Total'
  ,Name
  ,FIRST_VALUE(Street) OVER (PARTITION BY Acct ORDER BY Street DESC) AS 'Addr1'
  FROM TABLE_NAME
;

开窗口函数的好处是，你不需要在分组中添加函数中不需要的东西(例如SUM)，相反，你可以专注于描述你正在寻找的东西。

在上面的SQL中，我们说我们希望值的总和按Acct分组(或按OVER子句中的PARTITION by分组)。FIRST_VALUE允许使用返回街道地址的第一个值。同样没有日期时间列，所以很难说第一个值的顺序应该是什么。还有一个LAST_VALUE窗口函数。假设您确实有一个DATETIME列，您想要按该列值进行排序，如果没有，您可以像我对Street所做的那样选择一些值(MAX也可能是一个很好的选择，但拥有某种类型的DateTime值将是最好的方法)。

查看这个SQL Fiddle：http://sqlfiddle.com/#!6/a474c/8

下面是关于SUM使用OVER子句的BOL：http://msdn.microsoft.com/en-us/library/ms187810.aspx

以下是有关FIRST_VALUE的更多信息：http://blog.sqlauthority.com/2011/11/09/sql-server-introduction-to-first-_value-and-last_value-analytic-functions-introduced-in-sql-server-2012/

这是我写的一篇关于窗口函数的博客：http://comp-phil.blogspot.com/2013/03/higher-order-functions.html