Calculate % using OVER() for each row in SQL Server 2012

Question

I needed guidance with a question on how to calculate percentage between two cells on each row. I am on SQL Server 2012. Below is the structure of my current dataset.

Customer_Number    Price_Last_Year      Price_Today
---------------------------------------------------
909523                   154               190
20175808                 154               100
21808187                 154               190
22792798                 184               284
23256155                 154               230

Basically, what I am trying to do is to include an additional column that calculates the percentage of (Price_Today)/Price_Last_Year) for each specific row. I am using the windowing OVER() function and it has not worked properly.

This is how I want the my new dataset to look like:

Customer_Number Price_Last_Year     Price_Today     Percentage_AS ofToday%
909523                   154               190                 23.3%
20175808                 154                  100             -35.1%
21808187                 154               190                 23.3%
22792798                 184               284                 54.3%
23256155                 154               230                 49.3%

This my query for the percentage calculation:

SELECT DISTINCT
   Customer_Number,
   Price_Last_Year,
   Price_Today,
   [Percentage AS of Today%]=SUM(Price_Today)100.0/(Price_Last_Year) OVER()
FROM 
   tabl2 a
GROUP BY 
   Customer_Number, Price_Today, Price_Last_Year

I have tried a number of variations for the percentage OVER() calculation. I even tried something like [Percentage AS of Today%]=SUM(Price_Today)100.0/(Price_Last_Year) OVER() (PARTITION BY Customer_Number), and it throws an error.

How can I calculate the percentage using OVER() for each row?

It doesn't look to me like there's any reason for you to be using over, or sum. Just divide: `Price_Today / Price_Last_Year`. BTW, your math looks a little funny, unless I'm missing something. — Andrew, Jul 14 '15 at 16:46

score 1 · Answer 1 · answered Jul 14 '15 at 16:48

1

Given the data you supplied it looks like you have way overcomplicated this. The OVER seems unnecessary as does the GROUP BY. I think this will do what you want.

SELECT Customer_Number 
     , Price_Last_Year
     , Price_Today
     , (Price_Today)*100.0/(Price_Last_Year) - 100 as [Percentage AS of Today%]    
FROM tabl2 a

answered Jul 14 '15 at 16:48

Becuzz

6,846
26
39

Thank you for your answer. It helped me. – user3197575 Jul 16 '15 at 06:24

Mureinik · Accepted Answer · 2015-07-16T06:35:54.430

1

If your calculation is between two columns of the same row you don't need an over() clause or a group by clause - just divide the two. Subtracting this from 1 and multiplying by 100 should give you a nice percentage representation:

SELECT Customer_Number, 
       Price_Last_Year, 
       Price_Today, 
       (1 - (Price_Today/Price_Last_Year)) * 100 AS [Percentage AS of Today%]
FROM   tabl2

edited Jul 16 '15 at 06:35

answered Jul 14 '15 at 16:50

Mureinik

297,002
52
306
350

Thank you. I learnt something from you regarding when to use OVER(). – user3197575 Jul 16 '15 at 06:23

Calculate % using OVER() for each row in SQL Server 2012

2 Answers2