Need help in adding Percentage column

Question

Post reply

Need help in adding Percentage column

Vijji-429978

SSC Eights!

Points: 983

September 24, 2007 at 1:24 pm

#177414

Hi

I have a view which returns the data as below.

RegionDesc	MATotal	PartsTotal	DemandTotal	Total
1	7349.35	40763.52	6282.14	54395.01
2	28647.27	4093.57	55642.37	88383.21
3	64866.01	2601.77	138190.18	205657.96
4	219336.09	155.09	67716.4	287207.58
5	28293.00	4619.79	42894.18	75806.97

I need to add Percentage column next to the Total.

Calculation should be Sum(Total) / Total of each row.

RegionDesc	MATotal	PartsTotal	DemandTotal	Total	Percentage
1	7349.35	40763.52	6282.14	54395.01	13.08
2	28647.27	4093.57	55642.37	88383.21	8.05
3	64866.01	2601.77	138190.18	205657.96	3.46
4	219336.09	155.09	67716.4	287207.58	2.48
5	28293.00	4619.79	42894.18	75806.97	9.39

Please suggest me.

Thx

Vijji

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

how about this?

SELECT

RegionDesc,

MATotal,

PartsTotal,

DemandTotal,

Total,

(CASE WHEN ISNULL(Total,0.00) = 0 then 0

ELSE ISNULL(MATotal,0.00) +

ISNULL(PartsTotal,0.00) +

ISNULL(DemandTotal,0.00) / Total END) * 100 As Percentage

FROM SOMETABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vijji-429978 SSC Eights! Points: 983 More actions · Answer 2

Hi

calculations should be

Sum(Total) / Total of each row

Thx

vijji

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

Could you be more specific? Please show us how this calculation should be done using the data you provided in your original post.

😎

Vijji-429978 SSC Eights! Points: 983 More actions · Answer 4

Hi

I am adding calculation column.

RegionDesc	MATotal	PartsTotal	DemandTotal	Total	Percentage
1	7,349.35	40,626.75	6,282.14	54,258.24	13.11	(=711313.96 / 54258.24)
2	28,647.27	4,093.57	55,642.37	88,383.21	8.05	(=711313.96 / 88383.21)
3	64,866.01	2,601.77	138,190.18	205,657.96	3.46	(=711313.96 / 205657.96
4	219,336.09	155.09	67,716.40	287,207.58	2.48	(=711313.96 / 287207.58)
5	28,293.00	4,619.79	42,894.18	75,806.97	9.38	(=711313.96 / 75806.97)
			Total:	711,313.96

Thx

Vijji

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Try this:

;with TotalAmt (

GrandTotal

) as (

select

sum(Total)

from

dbo.yourView

)

select

RegionDesc,

MATotal,

PartsTotal,

DemandTotal,

Total,

cast(( GrandTotal/ Total) as decimal(5,2)) as Percent

from

dbo.yourView

cross join TotalAmt

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

For what it's worth - that's the INVERSE of a percentage.

Percent of total = group total/Grand total (NOT Grand total/Group Total).

Might as well save you the pain before an accounting type comes along and wonders why they don't add up to 100%=1....

Of course - other than that - Lynn's solution will work great.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

Just giving to OP what was requested. I'd prefer this for the percentage column:

cast((Total/GrandTotal) * 100 as decimal(5,2)) as Percent

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Understood Lynn - I noticed OP had calculation backwards. I should have specified WHO the comment was directed at. I haven't figured out the quote thingie in the new version of the boards yet:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vijji-429978 SSC Eights! Points: 983 More actions · Answer 9

Hi

<DIV id=_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl6_smAuthorName_SimpleMenuDivLayer onmouseover="InstantASP_OpenMenuMouseOver('_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl6_smAuthorName','_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl6_smAuthorName_SimpleMenuDivLayer','

View Member\'s Profile

Add to Buddy List

Send Private Message

Send Email To Member

Find All Member\'s Posts

','165px');" style="DISPLAY: inline" onclick="InstantASP_OpenMenu('_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl6_smAuthorName','_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl6_smAuthorName_SimpleMenuDivLayer','

View Member\'s Profile

Add to Buddy List

Send Private Message

Send Email To Member

Find All Member\'s Posts

','165px');">Lynn Pettis

It is working perfectly, thank you so much for your help.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

Actually, Matt, I was agreeing with you on your comment.