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 |
|
nkpriya
Starting Member
34 Posts |
 Posted - 2011-10-14 : 11:41:16
|
| I need to write a query for adding a column of totals.I have data like this:Sr Mgr Mgr Work Type Jan-11 Feb-11 Mar-11 Q1 2011Sr Mgr1 Mgr1 Business 100 100 100 300Sr Mgr1 Mgr1 Production 100 100 100 300Sr Mgr2 Mgr2 Production 100 100 100 300Sr Mgr2 Mgr2 Project 100 100 100 300Sr Mgr3 Mgr3 Project 100 1000 100 1200I need a column with percentage total for each worktype for each Mgr.Sr Mgr Mgr Work Type Jan-11 Feb-11 Mar-11 TotalSr Mgr1 Mgr1 Business 100 100 100 50%Sr Mgr1 Mgr1 Production 100 100 100 50%Sr Mgr2 Mgr2 Production 100 100 100 50%Sr Mgr2 Mgr2 Project 100 100 100 50%Sr Mgr3 Mgr3 Project 100 1000 100 100%Is there anyway to achieve this? Please let me know. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-14 : 11:46:26
|
| [code]SELECT *,[Q1 2011]*100/SUM([Q1 2011]) OVER (PARTITION BY [Sr Mgr],[Mgr]) AS [% of Total]FROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nkpriya
Starting Member
34 Posts |
Posted - 2011-10-14 : 12:00:27
|
| That worked. Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-14 : 12:05:25
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nkpriya
Starting Member
34 Posts |
Posted - 2011-10-14 : 12:19:25
|
| One more issue here. When I divide Sum(2011Q1), sometimes the value is zero. I am receiving Divide by zero error. Tried to use IsNull function but error message. LOoks like we cannot use IsNull with Partition. Please help! Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-14 : 12:21:11
|
it should be NULLIFSELECT *,[Q1 2011]*100/NULLIF(SUM([Q1 2011]) OVER (PARTITION BY [Sr Mgr],[Mgr]),0) AS [% of Total]FROM table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Percentage Totals