Help SQL statement create calculate percent ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help SQL statement create calculate percent ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-04-13 : 04:09:15

I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2

So, I need to create : ,

ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....

Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 04:24:58

If this is not what you want, please post your table structure

select ID, Answear1, Answear2, Answear3, Answear4,
       Vote1, Vote2, Vote3, Vote4,
       (Vote1 + Vote2 + Vote3 + Vote4) as Total,
       Vote1 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent1,
       Vote2 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent2,
       Vote3 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent3,
       Vote4 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent4
from   yourtable

KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-13 : 04:56:38

Also read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-04-14 : 04:13:02

Thank you so much !
But I store "Answear1, Answear2, Answear3, Answear4" in one field !
separate by "|".
Other ways to solve ?
Thanks.

quote:

Originally posted by khtan

If this is not what you want, please post your table structure

select ID, Answear1, Answear2, Answear3, Answear4,
       Vote1, Vote2, Vote3, Vote4,
       (Vote1 + Vote2 + Vote3 + Vote4) as Total,
       Vote1 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent1,
       Vote2 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent2,
       Vote3 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent3,
       Vote4 * 100.0 / (Vote1 + Vote2 + Vote3 + Vote4) as Percent4
from   yourtable

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-14 : 05:58:11

refer to following thread for the CSVTable fucntion to split the value. Modify the ',' to '|'
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-14 : 06:12:29

Also read about Normalisation. Use single column than multiple columns

Madhivanan

Failing to plan is Planning to fail

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-04-14 : 07:00:28

Thanks so much !!
Good link for me !

quote:
Originally posted by madhivanan

Also read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources