SQL Query to calculate Percentage between rows based on Columns values

SQL Query to calculate Percentage between rows based on Columns values

• Thursday, November 22, 2012 4:51 PM

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

All Replies

• Thursday, November 22, 2012 6:00 PM
Moderator

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

• Friday, November 23, 2012 3:46 AM

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.

• Friday, November 23, 2012 5:05 AM

Hi,

Ranking functions             Aggregate functions

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

• Friday, November 23, 2012 5:10 AM

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
------------------------------------
------------------------------
----------------------```

• Marked As Answer by Friday, November 23, 2012 9:31 AM
• Unmarked As Answer by Friday, November 23, 2012 9:32 AM
• Marked As Answer by Friday, November 23, 2012 9:33 AM
•
• Friday, November 23, 2012 9:35 AM

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

Thanks again for making this forums great,

Joss

• Friday, November 23, 2012 1:20 PM

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