Originally posted by Long John
View Post
Here are the queries if you want them:
LahmanCH.sql
Code:
SELECT t1.Taken, t1.KO, t1.playerID, t1.Last, t1.First, t1.Start, t1.Finish, t1.Period, t1.AB, t1.H, t1.HR, t1.RBI, t1.R, t1.SB, t1.BB, t1.HB, t1.SF, t1.PA, t1.AVG, c.G as C, ss.G as SS, 2b.G as 2B, of.G as OF, 3b.G as 3B, 1b.G as 1B, t1.x7 as "7", t1.x8 as "8", t1.x9 as "9", t1.x0 as "0", t1.x1 as "1", t1.x2 as "2", t1.x3 as "3", t1.x4 as "4", t1.x5 as "5", t1.x6 as "6", t1.x70 as "70", t1.x80 as "80", t1.x9 as "90", t1.x00 as "00", t1.x10 as "10" FROM (SELECT '' as Taken, 0 as KO, m.playerID as playerID, m.nameLast as Last, m.nameFirst as First, MIN(b.yearID) as Start, MAX(b.yearID) as Finish, IF(MAX(b.yearID) < 1899, "Deadball", IF(MIN(b.yearID) > 1998, "Contemporary", "Normal")) as Period, SUM(b.AB) as AB, SUM(b.H) as H, SUM(b.HR) as HR, SUM(b.RBI) as RBI, SUM(b.R) as R, SUM(b.SB) as SB, SUM(b.BB) as BB, SUM(b.HBP) as HB, SUM(b.SF) as SF, SUM(b.AB) + SUM(b.BB) + SUM(b.HBP) + SUM(b.SF) + SUM(b.SH) as PA, SUM(b.H) / sum(b.AB) as AVG, IF(MIN(b.yearID) < 1880, 1, 0) AS x7, IF(MIN(b.yearID) < 1890, IF(MAX(b.yearID) > 1879, 1, 0), 0) AS x8, IF(MIN(b.yearID) < 1900, IF(MAX(b.yearID) > 1889, 1, 0), 0) AS x9, IF(MIN(b.yearID) < 1910, IF(MAX(b.yearID) > 1899, 1, 0), 0) AS x0, IF(MIN(b.yearID) < 1920, IF(MAX(b.yearID) > 1909, 1, 0), 0) AS x1, IF(MIN(b.yearID) < 1930, IF(MAX(b.yearID) > 1919, 1, 0), 0) AS x2, IF(MIN(b.yearID) < 1940, IF(MAX(b.yearID) > 1929, 1, 0), 0) AS x3, IF(MIN(b.yearID) < 1950, IF(MAX(b.yearID) > 1939, 1, 0), 0) AS x4, IF(MIN(b.yearID) < 1960, IF(MAX(b.yearID) > 1949, 1, 0), 0) AS x5, IF(MIN(b.yearID) < 1970, IF(MAX(b.yearID) > 1959, 1, 0), 0) AS x6, IF(MIN(b.yearID) < 1980, IF(MAX(b.yearID) > 1969, 1, 0), 0) AS x70, IF(MIN(b.yearID) < 1990, IF(MAX(b.yearID) > 1979, 1, 0), 0) AS x80, IF(MIN(b.yearID) < 2000, IF(MAX(b.yearID) > 1989, 1, 0), 0) AS x90, IF(MIN(b.yearID) < 2010, IF(MAX(b.yearID) > 1999, 1, 0), 0) AS x00, IF(MIN(b.yearID) < 2020, IF(MAX(b.yearID) > 2009, 1, 0), 0) AS x10 FROM batting b INNER JOIN master m ON m.playerID = b.playerID GROUP BY b.playerID) t1 LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = 'C') c ON t1.playerID = c.playerID LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = '1B') 1b ON t1.playerID = 1b.playerID LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = '2B') 2b ON t1.playerID = 2b.playerID LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = 'SS') ss ON t1.playerID = ss.playerID LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = '3B') 3b ON t1.playerID = 3b.playerID LEFT JOIN (SELECT playerID, IF(sum(G) > 200, 1, 0) as G FROM fielding GROUP BY playerID, POS HAVING POS = 'OF') of ON t1.playerID = of.playerID;
Code:
SELECT '' as Taken, 0 as KO, m.playerID as playerID, m.nameLast as Last, m.nameFirst as First, MIN(p.yearID) as Start, MAX(p.yearID) as Finish, IF(MAX(p.yearID) < 1914, "Deadball", "Normal") as Period, SUM(p.IPouts) / 3 as IP, SUM(p.H) as H, SUM(p.ER) as ER, SUM(p.BB) as BB, SUM(p.SO) as SO, SUM(p.W) as W, SUM(p.G) as G, SUM(p.SV) as SV, SUM(p.ER) * 27 / SUM(p.IPouts) as ERA, (SUM(p.H) + SUM(p.BB)) * 3 / SUM(p.IPouts) as WHIP, SUM(p.L) as L, IF(MIN(p.yearID) < 1890, 1, 0) AS "78", IF(MIN(p.yearID) < 1900, IF(MAX(p.yearID) > 1889, 1, 0), 0) AS "9", IF(MIN(p.yearID) < 1910, IF(MAX(p.yearID) > 1899, 1, 0), 0) AS "0", IF(MIN(p.yearID) < 1920, IF(MAX(p.yearID) > 1909, 1, 0), 0) AS "1", IF(MIN(p.yearID) < 1930, IF(MAX(p.yearID) > 1919, 1, 0), 0) AS "2", IF(MIN(p.yearID) < 1940, IF(MAX(p.yearID) > 1929, 1, 0), 0) AS "3", IF(MIN(p.yearID) < 1950, IF(MAX(p.yearID) > 1939, 1, 0), 0) AS "4", IF(MIN(p.yearID) < 1960, IF(MAX(p.yearID) > 1949, 1, 0), 0) AS "5", IF(MIN(p.yearID) < 1970, IF(MAX(p.yearID) > 1959, 1, 0), 0) AS "6", IF(MIN(p.yearID) < 1980, IF(MAX(p.yearID) > 1969, 1, 0), 0) AS "7", IF(MIN(p.yearID) < 1990, IF(MAX(p.yearID) > 1979, 1, 0), 0) AS "8", IF(MIN(p.yearID) < 2000, IF(MAX(p.yearID) > 1989, 1, 0), 0) AS "99", IF(MIN(p.yearID) < 2010, IF(MAX(p.yearID) > 1999, 1, 0), 0) AS "00", IF(MIN(p.yearID) < 2020, IF(MAX(p.yearID) > 2009, 1, 0), 0) AS "10" FROM pitching p INNER JOIN master m ON m.playerID = p.playerID GROUP BY p.playerID;
Comment