| Author |
 Topic  |
|
|
MauerFan
Starting Member
3 Posts |
 Posted - 03/10/2013 : 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.WAR
FROM
(SELECT name, season, WAR
FROM fWAR_p
group by name, season, WAR)a
INNER JOIN
(SELECT name, season, WAR
FROM fWAR_p
group by name, season, WAR)b
ON a.name = b.name AND a.season = b.season -1
I'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 follows
WHERE WAR >= 18
Again, I am really new to this, so any other tips would be appreciated.
Contributor to Beyond the Box Score (beyondtheboxscore.com) |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/11/2013 : 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
India
47023 Posts |
Posted - 03/11/2013 : 01:08:36
|
is season a numeric field?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
MauerFan
Starting Member
3 Posts |
Posted - 03/11/2013 : 01:26:30
|
Visa- Yes it is. Like 2001, 2002, etc
Contributor to Beyond the Box Score (beyondtheboxscore.com) |
|
|
|
MauerFan
Starting Member
3 Posts |
Posted - 03/11/2013 : 01:30:22
|
Bandi-
Here's an example for Brandon Webb's individual #'s
Season WAR
2003 4.8
2004 3.1
2005 5.3
2006 7
2007 6.9
2008 6
2009 -0.2
I 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) |
Edited by - MauerFan on 03/11/2013 01:30:55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/11/2013 : 01:48:05
|
something like
SELECT name
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY Season) AS Seq,*
FROM fWAR_p
)t
WHERE seq<=6
GROUP BY name
HAVING SUM(WAR) > = 18
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Question with JOINS
