Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-07-14 : 08:46:33
|
Month Title TotalJune A 1069June B 1175I would like the Title A to calculate as below:Title A / Title B * 100 to get in percentage. Result should be as below:Month Title TotalJune A 91%June B 1175how can i do that? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-14 : 13:39:05
|
[code]SELECT [Month],Title,Total*100.0/SUM(Total) OVER (PARTITION BY [Month]) AS [Total%]FROM Table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-14 : 21:35:46
|
[code]SELECT Month, Title, CASE WHEN Title = 'A' THEN 100.0*Total/SUM(CASE WHEN Title=B THEN Total ELSE 0 END) OVER(PARTITION BY MONTH) ELSE Total ENDFROM Tbl;[/code] |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-07-14 : 21:40:59
|
I tried James method but the result I get 0.visakh16 method the total are incorrect. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-14 : 22:26:21
|
I edited my previous post. See change in red |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-15 : 00:37:21
|
quote: Originally posted by peace I tried James method but the result I get 0.visakh16 method the total are incorrect.
so you just want percentage only if its Title A?then you can do this small modificationSELECT [Month],Title,CASE WHEN Title='A' THEN Total*100.0/SUM(CASE WHEN Title='B' THEN Total ELSE 0 END) OVER (PARTITION BY [Month]) ELSE Total END AS [Total]FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|