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 |
MauerFan
Starting Member
3 Posts |
Posted - 2013-03-10 : 21:00:30
|
Hello all, I am relatively new to SQL and I have a few questions. I am trying to write a query for all pitchers that started their careers by being completely dominant, and then falling off. I'm using a statistic called "WAR", and I'm trying to write a query to see how many pitchers had 18 or more WAR in their first six seasons, and then did not have any more success the rest of their career. Right now this is the code I have, and it's not very good:SELECT a.name, a.season, a.WAR, b.name, b.season, b.WARFROM(SELECT name, season, WARFROM fWAR_pgroup by name, season, WAR)aINNER JOIN(SELECT name, season, WARFROM fWAR_pgroup by name, season, WAR)bON a.name = b.name AND a.season = b.season -1I'm stuck as to how I would code for the first six seasons, one I get that I would imagine i would make my WHERE statement as followsWHERE WAR >= 18Again, I am really new to this, so any other tips would be appreciated.Contributor to Beyond the Box Score (beyondtheboxscore.com) |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 00:24:44
|
Can you post sample data for the table fWAR_p and also expected output what you supposed get?--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 01:08:36
|
is season a numeric field?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MauerFan
Starting Member
3 Posts |
Posted - 2013-03-11 : 01:26:30
|
Visa- Yes it is. Like 2001, 2002, etcContributor to Beyond the Box Score (beyondtheboxscore.com) |
|
|
MauerFan
Starting Member
3 Posts |
Posted - 2013-03-11 : 01:30:22
|
Bandi-Here's an example for Brandon Webb's individual #'sSeason WAR2003 4.82004 3.12005 5.32006 72007 6.92008 62009 -0.2I want to get those numbers added up though, and show players with similar careers with the parameters set above.Contributor to Beyond the Box Score (beyondtheboxscore.com) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 01:48:05
|
something likeSELECT nameFROM(SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY Season) AS Seq,*FROM fWAR_p)tWHERE seq<=6GROUP BY nameHAVING SUM(WAR) > = 18 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|