February 22, 2021 at 4:35 pm
I have a query that calculates revenue by Region per month, per year. So, the end result should look something like:
Region Year Month Revenue
---------------------------------
Europe 2021 01 20%
Europe 2021 02 21%
Americas 2021 01 80%
Americas 2021 02 79%
The last column is produced by:
,CONCAT(
CAST(ROUND(Revenue/SUM(Revenue) OVER(),4)*100 AS DECIMAL (19,2))
, '%') AS Revenue
Now, the trouble with this is that it works out contribution of each row to the whole regardless of the month number, whereas I would like it to look at each month and calculate contribution per month, like in the example above.
I just can't think of how to do this. I've tried to duplicate the code changing the month number in the WHERE clause and then do a UNION ALL but this is not the most elegant solution and will get even more ridiculous as more months pass. Any help on how to tackle it would be appreciated.
February 22, 2021 at 4:59 pm
SUM(Revenue) OVER( PARTITION BY Region, Year, Month )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply