如何在SQL中查找聚合总计 [英] How to find aggregate total in SQL

问题描述

我编写了以下查询以获得下表所示的结果

I wrote following query to get the result shown in below table

SELECT EOMONTH (ADateTime) ADateTime, 
				CAST (SUM(ISNULL(Profit,0)) AS decimal(18,3)) Profit
				FROM CostLedger
				WHERE ISNULL(Profit,0)>0 AND YEAR(ADatetime)=YEAR(CAST ( '2017-May-01' as Date))
				GROUP BY  EOMONTH (ADateTime)

结果

RESULTS

ADateTime	Profit
2017-03-31	17.386
2017-04-30	180.194
2017-05-31	158.983
2017-06-30	915.255

我需要得到每一行的总和如

I need to get the aggregate sum in each row as like

ADateTime  Profit          Total_Profit
2017-03-31	17.386	        17.386
2017-04-30	180.194	        197.58  (Added Previous Row Profit with Current Row)
2017-05-31	158.983	        356.563
2017-06-30	915.255	        1271.818

任何帮助....重写我的剧本？



我尝试过：

Any Help....to rewrite my script?

What I have tried:

SELECT EOMONTH (ADateTime) ADateTime, 
				CAST (SUM(ISNULL(Profit,0)) AS decimal(18,3)) Profit
				FROM CostLedger
				WHERE ISNULL(Profit,0)>0 AND YEAR(ADatetime)=YEAR(CAST ( '2017-May-01' as Date))
				GROUP BY  EOMONTH (ADateTime)

获取第一个表结果

to get the first table result

推荐答案

取决于您使用的SQL版本。对于SQL 2012及更高版本，请尝试：

Depends on the version of SQL you are using. For SQL 2012 and later try:

SELECT a.ADateTime, a.Profit,
       SUM(Profit) OVER(ORDER BY EOMONTH(ADateTime)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
           AS Balance
FROM (SELECT EOMONTH (ADateTime) ADateTime, 
			 CAST (SUM(ISNULL(Profit,0)) AS decimal(18,3)) Profit
	  FROM CostLedger
	  WHERE ISNULL(Profit,0)>0 AND YEAR(ADatetime)=YEAR(CAST ( '2017-May-01' as Date))
	  GROUP BY  EOMONTH (ADateTime)) a

这篇关于如何在SQL中查找聚合总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！