时间戳

首页 > 解决方案 > 特定行的总和

sql-server - 特定行的总和

问题描述

我有一些这样的查询

SELECT TotalAmount/(select DateDiff(dd,StartDate, DateAdd(dd, 1,EndDate))) AS TotalAmountForMonth FROM Campaign WHERE StartDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND StartDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

此查询搜索本月的活动，并从 TotalAmount 获取每日付款作为行。但我需要他们的总和（总）数。

问题是，我怎样才能得到这个查询的总和？

Edit1：表格示例

Total Amount StartDate EndDate 1980 2018-05-01 00:00:00.0000000 2018-05-31 00:00:00.0000000 1271 2018-05-01 00:00:00.0000000 2018-06-01 00:00:00.0000000 2015 2018-05-01 00:00:00.0000000 2018-06-01 00:00:00.0000000 238 2018-05-01 00:00:00.0000000 2018-05-08 00:00:00.0000000 0 2018-05-01 00:00:00.0000000 2018-06-01 00:00:00.0000000

而他们的结果

63,8709677419355 39,71875 62,96875 29,75 0

标签： sql-servertsql

解决方案

这将需要SUM聚合函数。

Select SUM(TotalAmountForMonth) from (
SELECT TotalAmount/(select DateDiff(dd,StartDate, DateAdd(dd, 1,EndDate)))
 AS TotalAmountForMonth
FROM Campaign 
WHERE StartDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
  AND StartDate <  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
)

推荐阅读