Menu
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)?
531
The other option for combining aggregate functions in SQL is using a CTE instead of a subquery. A CTE is a tidier and “closer to the mathematical logic” version of a subquery. It is an expression that allows you to create a temporary result, which you can reference in another SELECT statement.
Aggregate functions operate on many records and produce a summary, works with GROUP BY whereas non-aggregate functions operate on each record independently. There are so many built-in functions in SQL to do various calculations on data.
For example, if you have a Price variable, the aggregated field may be the Sum, Average, Maximum, Minimum, or Count of the number of rows that the Price variable has. A non-aggregated field is the expanded data of the field, it's the rows that make up the whole Price variable.
GROUP BY without Aggregate Functions Although most of the times GROUP BY is used along with aggregate functions, it can still still used without aggregate functions — to find unique records.
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.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
