CODEWITHSUNDEEP
sqlsql-serversum
PeterH
PeterH

Reputation: 1040

create a dynamic SUM column in SQL

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

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

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.

Hope i explained this ok ?

Upvotes: 2

Views: 297

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use window functions:

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 ;

Upvotes: 4

Related Questions