Increment Header Column 1-20

• Jay Bowers

  SSC Veteran

  Points: 227

  More actions

  May 17, 2010 at 2:45 pm

  #235182

  I have a known number of integer values for a dataset. What I would like is the possible values (integers 1 through 20) in the first column, and the number of people who got that score in the second column. Is there a way to populate the first column to increment from 1 to 20, instead of making 20 rows?

  Then, how do I pull the Count(PriorRowValue) into the picture?

  Just wondering if anyone has tackled this before ...

  Score Number of Students

  1 0

  2 0

  3 1

  4 2

  5 5

  6 2

  7 8

  ...

  18 5

  19 0

  20 1

  Thanks in advance.

• booshi

  SSC Enthusiast

  Points: 151

  More actions

  May 24, 2010 at 9:52 am

  #1171647

  To get the integers in the first column use a query like this:

  select ROW_NUMBER() OVER (ORDER BY NumOfStudents) AS Score, NumOfStudents from TableName

  Or, alternatively, to make things easier in the future, simply add a column to the table for score. It would make things easier as for each assignment a query like this would work perfectly:

  SELECT COUNT(*) AS NumberOfStudents, Score FROM TableName WHERE Assignment = "AssignmentName" GROUP BY Score