Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dineshkunder
Starting Member
9 Posts |
 Posted - 2005-07-23 : 14:28:19
|
| Thanks in advance and help greatly apprciated!!I am using the foll. query(shorter version below, returning around twenty computed columns) to display Baseball stats on a website, basically the sums of certain fields returned in a cursor. How would I also obtain the Max and Min of all columns below in addition to the Sums(indicating which player for both)? I get an error if I use the Max function on the subquery below...Any way of obtaining this info in the same select below or in a different select. Either way or rather the most efficient way would be great!select player_firstname, player_id,(select sum(ab) from stats_hitting where stats_hitting.player_id = profile_players.player_id ) as AB,(select sum(h) from stats_hitting where stats_hitting.player_id = profile_players.player_id ) as Hfrom profile_players-------------I get the error 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.' when I use the foll.select player_firstname, player_id,MAX((select sum(ab) from stats_hitting where stats_hitting.player_id = profile_players.player_id )) as ABfrom profile_players |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-23 : 17:42:59
|
| You could probably do this a few different ways. Here is one:SELECT X.player_firstname, X.player_id, MAX(X.ab), MAX(X.h) FROM(SELECT profile_players.player_firstname, profile_players.player_id, SUM(ab) AS ab, SUM(h) AS h FROM profile_players JOIN stats_hitting ON profile_players.player_id = stats_hitting.player_idGROUP BY profile_players.player_firstname, profile_players.player_id) AS X GROUP BY X.player_firstname, X.player_id And as an aside, how can I get a job like yours? I've always wanted to be able to manage and write queries for a sports statistics database! |
 |
|
|
dineshkunder
Starting Member
9 Posts |
Posted - 2005-07-23 : 21:43:37
|
| Thanks for the solution!! I am posting another question...Actually I wish it were a job, but it's just a website I am creating for a league that I play in, it's more like a bunch of regular guys who get together every weekend to play! |
|
|
|
|
|
|
|
|
Aggregate Function on another aggregate function