SQL Query to calculate Percentage between rows based on Columns values

Question

Hi there,

For the following example:

I would like to calculate a percentage factor of the Value column for different rows when the ColumnKey1 between these rows is the same and the ColumnKey3 between these rows is the same and the ColumnKey2 between these rows is different, so for the example in the image above, the yellow highlighted rows are meeting the requirement, so the Factor would be calculated as following:

Factor = Row Value / Total Value for the rows that  meet the requirement

Any advice would be very welcome!!

Thanks in adavance and best regards,

Joss

Answer 1

select *, Value / NULLIF(SUM(Value) OVER (partition by ColumnKey1, ColumnKey2),0) as Factor

from myTable

--------------------------

This solution does not check if the ColumnKey2 value is different or not.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi, You can use window functions calculate a group total and figure a row number max to get a group total over group population mean.

WITH cte1 AS 
(
SELECT c1, c2, c3, val
, SUM(val) OVER (PARTITION BY c1,c3) AS [GrpTotal]
, ROW_NUMBER() OVER (PARTITION BY c1,c3 ORDER BY c2) AS [GrpCount]
 FROM t01
 )
 SELECT c1, c2, c3, val, [GrpTotal], [GrpCount]
 , MAX([GrpCount]) OVER (PARTITION BY c1,c3) [GrpMaxCount]
 ,[GrpTotal] / MAX([GrpCount]) OVER (PARTITION BY c1,c3) [GrpAvg]
 FROM cte1

---

If you're happy and you know it vote and mark.

Answer 3

Hi,

Please see below link hope it will help You:

Ranking functions             Aggregate functions  

http://msdn.microsoft.com/en-us/library/ms189461.aspx

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 4

Try the below one to get the exact answer.

DECLARE @TAB AS TABLE (COLUMNKEY1 INT,COLUMNKEY2 INT,COLUMNKEY3 INT,VALUE INT)
INSERT INTO @TAB VALUES(1,2,5,10) ,(1,3,5,20),(1,4,2,40)
SELECT * FROM @TAB
-----------------------------------
SELECT *,CAST(VALUE AS DECIMAL(38,2))/NULLIF(SUM(VALUE) OVER(PARTITION BY COLUMNKEY3,COLUMNKEY1),0) AS FACTOR   FROM @TAB
------------------------------------ 
------------------------------
----------------------

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

Thank you very much, I am going to add OVER to my head-repository!

Thanks again for making this forums great,

Joss

Answer 6

Below is the query 

select cl1,cl2,cl3,case when chk =1 then value/GrpTotal else 1 end as factor from (
select cl1,cl2,cl3,case when cl1>cl2 and cl2>cl3 then 1 else 0 end as chk, SUM(val) OVER (PARTITION BY chk) AS [GrpTotal]
,value from table)

---

SQL Champ
Database Consultants NY