在 SQL 中创建动态 SUM 列 [英] create a dynamic SUM column in SQL

问题描述

我有以下查询，它返回、商店编号及其地区，以及 2016 年商店销售额的总和.

I have the below query which returns, store number and its region, and the sum of the stores sales in 2016.

SELECT
EU.[STORE NO]                               
,EU.REGION
,SUM(SA.SALESEXVAT) AS 'STORE SALES'


FROM
[BHXSQL2014-DEV].BManalytics.dbo.EUactivestores EU

INNER JOIN
EUUKSQL01.dashboard.dbo.stocksalesaggregateweek sa 
ON  eu.[Store No]   =    sa.[branchno] 

WHERE
sa.Fiscalyear  =     2016


GROUP BY 
EU.[Store No]   
,EU.REGION  

我将如何添加第 4 列来汇总每个商店所在地区的销售额.

How would i add a 4th Column that would sum up the sales of the REGION that each store is in.

每个区域有很多要存储的，所以这个值在每个在同一区域有商店的 ROW 上都是一样的.

There are many to stores to each region, so this value would be the same on each ROW that has a store in the same region etc.

希望我解释清楚了吗?

推荐答案

可以使用窗口函数:

SELECT EU.[STORE NO], EU.REGION,
       SUM(SA.SALESEXVAT) AS STORE_SALES
       SUM(SUM(SA.SALESEXVAT)) OVER (PARTITION BY EU.REGION) as REGION_STORE_SALES
FROM [BHXSQL2014-DEV].BManalytics.dbo.EUactivestores EU INNER JOIN
      EUUKSQL01.dashboard.dbo.stocksalesaggregateweek sa 
      ON eu.[Store No] = sa.[branchno] 
WHERE sa.Fiscalyear  =     2016
GROUP BY EU.[Store No], EU.REGION ; 

这篇关于在 SQL 中创建动态 SUM 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！