percentage calculation - 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

percentage calculation

Author

Topic

JacekK
Starting Member

14 Posts

Posted - 2011-01-10 : 16:44:24

How do i calculate percentage based on a group by statement. I have a road condition table, which is divided by specific areas and conditions. I need to group by area and condition and calculate segment's length percentage to the area.

Table:

from to area cond
0 20 1 1
20 50 1 2
50 100 1 1
100 250 1 2
250 300 2 2
300 350 2 2
350 475 2 2
475 700 2 1

desired Result:

from to area cond Percentage
0 20 1 1 8
20 50 1 2 12
50 100 1 1 20
100 250 1 2 60
250 300 2 2 11.11111111
300 350 2 2 11.11111111
350 475 2 2 27.77777778
475 700 2 1 50

Thanks!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-10 : 17:50:07

Here is one way:

--Setup
DECLARE @T TABLE([from] int, [to] int, area int, cond int)

INSERT @T ([from], [to], area, cond)
VALUES
(0, 20, 1, 1),
(20, 50, 1, 2),
(50, 100, 1, 1),
(100, 250, 1, 2),
(250, 300, 2, 2),
(300, 350, 2, 2),
(350, 475, 2, 2),
(475, 700, 2, 1)

-- Select results
SELECT 
    *,
    (([to] - [from]) / ((MAX([To]) OVER (PARTITION BY Area) - MIN([From]) OVER (PARTITION BY Area)) * 1.00)) * 100 AS Percentage
FROM @T

JacekK
Starting Member

14 Posts

Posted - 2011-01-11 : 11:30:41

Great! Thanks a lot!!!

How about one more level of complexity.

How about grouping these results by area and cond based on these rules:

keep cond = 1 when cumulative sum of percentages for all segments > 30 and cond = 1,

otherwise keep cond = 2. So the result would look like this:

DECLARE @T2 TABLE([from] int, [to] int, area int, cond int)
INSERT @T2 ([from], [to], area, cond)
VALUES
(0, 250, 1, 2),
(250, 700, 2, 1)

Thanks again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources