SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question with JOINS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MauerFan
Starting Member

3 Posts

Posted - 03/10/2013 :  21:00:30  Show Profile  Reply with Quote
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
2206 Posts

Posted - 03/11/2013 :  00:24:44  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/11/2013 :  01:08:36  Show Profile  Reply with Quote
is season a numeric field?

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

Go to Top of Page

MauerFan
Starting Member

3 Posts

Posted - 03/11/2013 :  01:26:30  Show Profile  Reply with Quote
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 - 03/11/2013 :  01:30:22  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/11/2013 :  01:48:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000