CODEWITHSUNDEEP
sqlaggregate-functions
hadley
hadley

Reputation: 103898

Combined aggregated and non-aggregate query in SQL

Not sure how to phrase this question, but I want an aggregate query applied to multiple rows. Hopefully an example should make this easier. Assuming I have the 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 for each player in each year, I want to calculate their "career year", i.e. the 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 would be natural to express this transformation as SELECT player, year, games, year - min(year) + 1 as cyear FROM baseball GROUP by player but because of the rules for aggregate queries the expression is only evaluated once for each group:

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

How can I overcome this problem in general (i.e. not just for this case but whenever I want to perform an arithmetic operation combining an existing column and a single per-group number computed with an aggregate function)?

Upvotes: 5

Views: 3336

Answers (4)

Danyel Fisher
Danyel Fisher

Reputation: 53

If you don't have WITH or OVER, then ... get a real database. Failing that, you can do it with a subquery:

SELECT t.*, t.year - subtable.minyear + 1 AS cyear
FROM table AS t
JOIN (
  select player, min(year) as minyear 
  from table 
  group by player
) AS SubTable
ON T.player = SubTable.player

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

You can use ROW_NUMBER for the career-year:

SELECT player, year, games,
       cyear = ROW_NUMBER () OVER (PARTITION BY player ORDER BY year),
       gamesPerMax = 1.0 * games / MAX(games) OVER (PARTITION BY player)
FROM dbo.TableName

Demo

Have a look at the powerful OVER clause.

Upvotes: 4

Eugene Wu
Eugene Wu

Reputation: 31

One straightforward method is to compute each player's starting year as an aggregate query, and join the data with the original. These kinds of "sequence based" queries are usually tricky to express in a set based language :(

WITH tmp as (
  select player, min(year) as minyear 
  from table 
  group by player
);

select t.*, t.year - t.minyear + 1 as cyear
from table as t, tmp
where t.player = tmp.player;

Upvotes: 3

dartdog
dartdog

Reputation: 10862

Just use multiple group by... and sum on the desired field

GROUP BY player, year

Upvotes: 0

Related Questions