Trying to compute equal 20% distributions

• cramser

  Grasshopper

  Points: 19

  More actions

  February 9, 2009 at 12:42 pm

  #72382

  I've been computing a distribution of values in SQL, then taking that distribution into Excel to calculate the cumulative distribution so I can make splits at 20% (or as close to 20% as possible). Here's a sample:

  Frequency Count % Cume %

  1 10 5% 5%

  2 10 5% 10%

  3 20 10% 20%

  4 10 5% 25%

  etc...

  I manually figure out where the decile (20%) cuts are, then perform a update command in SQL to assign a value based on each of these 20% segments. i.e. first 20% gets a "1", 2nd 20% gets a "2" & so on..

  I'd like to have SQL figure out equal cuts based on the cumulative distributions so that the lowest 20% gets assigned a "1", the next 20% gets a "2" & so on. It must be somewhat flexible as the cumulative distributions don't always break on an even 20%. In those cases, I need SQL to take the distribution at each frequency of distribution. Example

  Frequency Count % Cume % Assignment

  1 100 25% 25% 1

  2 75 17% 42% 2

  3 80 18% 60% 3

  etc...

  Need further detail? Anyone wat to take a stab at it?

• Jack Corbett

  SSC Guru

  Points: 184397

  More actions

  February 9, 2009 at 2:53 pm

  #940851

  Could you post some structures and some test data (see the links in my signature)?

  Also can you verify that you are using SQL Server 2000? That definitely would affect any solution proposed.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question