Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TOP 25% rows by Weightage Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:05 AM
Points: 9, Visits: 28
I have a table as below.
I need to find the members which are in the bottom quartile (25%) of the weights in each class

ClassID	MemberID	Weight 
A 1 10
A 2 20
A 3 40
A 5 25
A 6 15
A 8 10
B 1 50
B 2 10
B 3 60
B 6 400


For above example, total weight of class A is 120. If I order by weight and pick the top ones such that their summed weight comprise less than or equal to 25% of total weight (25% of 120 = 30 for class A)
I would pick Members 1 and 8. If I include member 6, it would exceed 25% (30)

The output I need is

ClassID	MemberID	Weight 
A 1 10
A 8 10
B 1 50
B 2 10
B 3 60


What would be the best way of achieving this in TSQL (Sql server 2008)?
Hope the question is clear
Post #1443331
Posted Wednesday, April 17, 2013 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
You can do this using a running total. Take a look at this article that explains how to do a running total in sql.

http://www.sqlservercentral.com/articles/T-SQL/68467/

If you get stuck feel free to post back here. It is best if you can provide data in a consumable format. Since you are brand new around here I did this for you so you can see.

;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443354
Posted Wednesday, April 17, 2013 10:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
If you're using SQL Server 2012, you can do this

;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
),
CTE AS (
SELECT ClassID,MemberID,Weight,
SUM(Weight) OVER(PARTITION BY ClassID ORDER BY Weight) AS RunningTotalWeight,
SUM(Weight) OVER(PARTITION BY ClassID) AS TotalWeight
FROM Data)
SELECT ClassID,MemberID,Weight
FROM CTE
WHERE RunningTotalWeight <= TotalWeight/4
ORDER BY ClassID,MemberID;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1443360
Posted Thursday, April 18, 2013 1:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:05 AM
Points: 9, Visits: 28
Thanks very much guys...Unfortunately I'm still on 2008

I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article
;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)
--SELECT* FROM Data ORDER BY ClassID,Weight
SELECT d.classid,d.memberid,d.Weight
FROM Data d
INNER JOIN (
SELECT D2.ClassID,SUM(Weight) as total_weight
FROM Data D2
GROUP BY D2.ClassID
) Totals ON Totals.ClassID = d.ClassID
WHERE
(SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight)
<= 0.25*Totals.total_weight

Help is much appreciated
Post #1443626
Posted Thursday, April 18, 2013 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
chintan.floydian (4/18/2013)
Thanks very much guys...Unfortunately I'm still on 2008

I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article
;with Data(ClassID, MemberID, Weight) as
(
select 'A', 1, 10 union all
select 'A', 2, 20 union all
select 'A', 3, 40 union all
select 'A', 5, 25 union all
select 'A', 6, 15 union all
select 'A', 8, 10 union all
select 'B', 1, 50 union all
select 'B', 2, 10 union all
select 'B', 3, 60 union all
select 'B', 6, 400
)
--SELECT* FROM Data ORDER BY ClassID,Weight
SELECT d.classid,d.memberid,d.Weight
FROM Data d
INNER JOIN (
SELECT D2.ClassID,SUM(Weight) as total_weight
FROM Data D2
GROUP BY D2.ClassID
) Totals ON Totals.ClassID = d.ClassID
WHERE
(SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight)
<= 0.25*Totals.total_weight

Help is much appreciated


There are a few things you would have to do differently here for the quirky update to work. You would need to move this to a table instead of a cte. You need to have a clustered index as described in the article. The idea here is that you could use the quirky update to get your running total and then select rows where the total column is less than max of that column * 25.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse