Get percent column - query - SQL Server Forums

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

Get percent column - query

Author

Topic

tiss0183
Starting Member

18 Posts

Posted - 2008-01-17 : 19:57:29

I have a data containing salesman who can belong to groups. The groups have "split" values but the splits are not on a percent basis. Can I create a query to get the percentage in a column? Please look at the table below and my attempted query.

Salesman Group Split SplitPercent
A G1 1 0.33
B G1 1 0.33
C G1 1 0.33
Y G2 0.5 0.5
Z G2 0.5 0.5

UPDATE Splits
SET SplitPercent = Split / (SELECT SUM(Split) FROM Splits GROUP BY Splits.Group HAVING Splits.Group = ???)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 02:24:49

See if you get correct result

select *,Split*1.0 / (SELECT SUM(Split) FROM Splits where Group =T.Group) from Splits T

Madhivanan

Failing to plan is Planning to fail

tiss0183
Starting Member

18 Posts

Posted - 2008-01-19 : 18:10:46

It works.
Thank you very much.

Here is the whole update statement:

UPDATE Splits
SET SplitPercent =
(SELECT Split /
(SELECT SUM(Split) FROM Splits WHERE Grouping = T.Grouping)
FROM Splits T
WHERE Splits.Salesman = T.Salesman
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources