Combined aggregated and non-aggregate query in SQL -

not sure how phrase question, want aggregate query applied multiple rows. example should make easier. assuming have following data:

  player  | year | games ------------------------- ausmubr01 | 2006 | 139 ausmubr01 | 2007 | 117 bondsba01 | 2006 | 130 bondsba01 | 2007 | 126 stairma01 | 2006 | 26 stairma01 | 2006 | 77 stairma01 | 2006 | 14 stairma01 | 2007 | 125 

and each player in each year, want calculate "career year", i.e. number of years they've been playing:

  player  | year | games | cyear  -------------------------------- ausmubr01 | 2006 | 139   |  1 ausmubr01 | 2007 | 117   |  2 bondsba01 | 2006 | 130   |  1 bondsba01 | 2007 | 126   |  2 stairma01 | 2006 | 26    |  1 stairma01 | 2006 | 77    |  2 stairma01 | 2006 | 14    |  3 stairma01 | 2007 | 125   |  4 

it natural express transformation select player, year, games, year - min(year) + 1 cyear baseball group player because of rules aggregate queries expression evaluated once each group:

  player  | year | games | cyear  -------------------------------- ausmubr01 | 2006 | 139   |  1 bondsba01 | 2006 | 130   |  1 stairma01 | 2006 | 26    |  1 

how can overcome problem in general (i.e. not case whenever want perform arithmetic operation combining existing column , single per-group number computed aggregate function)?

you can use row_number career-year:

select player, year, games,        cyear = row_number () on (partition player order year),        gamespermax = 1.0 * games / max(games) on (partition player) dbo.tablename 

demo

have @ powerful over clause.