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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question with JOINS

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.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
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 01:08:36
is season a numeric field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MauerFan
Starting Member

3 Posts

Posted - 2013-03-11 : 01:26:30
Visa- Yes it is. Like 2001, 2002, etc

Contributor to Beyond the Box Score (beyondtheboxscore.com)
Go to Top of Page

MauerFan
Starting Member

3 Posts

Posted - 2013-03-11 : 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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 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/

Go to Top of Page
   

- Advertisement -