Just a couple of quick bits:
For the team batting report I used a switch/case statement (I guess in SQL it's just a CASE) to change the position IDs to position names, and I also added a filter for positions so if you really want to output pitcher hitting you can. The whole code below:
Code:
SET @year = 1969, @team_abbr='NYY', @positions='3,4,5,6';
SELECT
CASE
WHEN fld.position = 2 THEN "C"
WHEN fld.position = 3 THEN "1B"
WHEN fld.position = 4 THEN "2B"
WHEN fld.position = 5 THEN "3B"
WHEN fld.position = 6 THEN "SS"
WHEN fld.position = 7 THEN "LF"
WHEN fld.position = 8 THEN "CF"
WHEN fld.position = 9 THEN "RF"
ELSE "DH"
END as pos,
plr.first_name,
plr.last_name,
bat.g,
bat.ab,
bat.r,
bat.h,
bat.d as 2B,
bat.t as 3B,
bat.hr,
bat.rbi,
bat.bb,
bat.ibb,
bat.k,
bat.hp as hpb,
bat.sh,
bat.sf,
bat.sb,
bat.cs,
bat.gdp,
round(bat.h / bat.ab, 3) as ba,
round((bat.h + bat.bb + bat.hp) / (bat.h + bat.bb + bat.hp + bat.sh + bat.sf), 3) as obp,
round((bat.h + bat.d + 2 * bat.t + 3 * bat.hr) / ab, 3) as slg
FROM `modern-ish-baseball`.teams tm
LEFT JOIN players_career_batting_stats bat
ON bat.team_id = tm.team_id
LEFT JOIN players plr
ON plr.player_id = bat.player_id
LEFT JOIN players_career_fielding_stats fld
ON fld.player_id = plr.player_id and fld.year = @year and bat.team_id = fld.team_id
where tm.abbr = @team_abbr
AND bat.year = @year
AND bat.split_id = 1
AND fld.ip = (select max(ip) from `modern-ish-baseball`.players_career_fielding_stats f
WHERE f.year = @year and f.team_id = tm.team_id and f.player_id = plr.player_id)
AND find_in_set(fld.position, @positions)
ORDER BY fld.position, bat.pa desc
And, I even discovered that the Workbench has an "output to text" function that means that there's no need to play with formatting if you just want to copy and paste into a code tag:
Code:
+ -------- + --------------- + -------------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
| pos | first_name | last_name | g | ab | r | h | 2B | 3B | hr | rbi | bb | ibb | k | hpb | sh | sf | sb | cs | gdp | ba | obp | slg |
+ -------- + --------------- + -------------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
| 1B | Justin | Richens | 63 | 231 | 34 | 71 | 17 | 2 | 9 | 26 | 41 | 3 | 14 | 6 | 0 | 5 | 0 | 0 | 6 | 0.307 | 0.959 | 0.515 |
| 1B | Jose | Ayala | 51 | 193 | 24 | 55 | 9 | 0 | 5 | 23 | 13 | 2 | 34 | 1 | 0 | 4 | 2 | 0 | 10 | 0.285 | 0.945 | 0.409 |
| 1B | John | Chapman | 60 | 138 | 13 | 31 | 6 | 0 | 3 | 14 | 26 | 1 | 16 | 1 | 0 | 2 | 0 | 0 | 0 | 0.225 | 0.967 | 0.333 |
| 1B | Bobby | Berg | 12 | 23 | 7 | 8 | 2 | 0 | 2 | 8 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0.348 | 1.000 | 0.696 |
| 2B | Wing-fung | Yi | 105 | 383 | 44 | 117 | 17 | 6 | 6 | 45 | 67 | 5 | 29 | 2 | 0 | 3 | 12 | 15 | 4 | 0.305 | 0.984 | 0.428 |
| 2B | Elijah | Patton | 38 | 92 | 10 | 19 | 4 | 0 | 0 | 12 | 9 | 3 | 13 | 0 | 0 | 2 | 0 | 1 | 5 | 0.207 | 0.933 | 0.250 |
| 2B | Chris | Eckert | 16 | 33 | 4 | 7 | 1 | 0 | 0 | 3 | 4 | 2 | 7 | 0 | 0 | 0 | 0 | 0 | 2 | 0.212 | 1.000 | 0.242 |
| 2B | Nate | Lieb | 3 | 9 | 1 | 2 | 0 | 0 | 1 | 2 | 2 | 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0.222 | 1.000 | 0.556 |
| 3B | Tom | Weiss | 128 | 423 | 62 | 115 | 12 | 2 | 17 | 69 | 60 | 3 | 38 | 7 | 1 | 3 | 0 | 0 | 8 | 0.272 | 0.978 | 0.430 |
| 3B | David | Salinas | 49 | 169 | 14 | 36 | 5 | 0 | 1 | 18 | 15 | 0 | 12 | 1 | 0 | 2 | 2 | 2 | 8 | 0.213 | 0.963 | 0.260 |
| SS | Ty | Stover | 146 | 530 | 85 | 127 | 24 | 6 | 25 | 92 | 96 | 1 | 105 | 3 | 0 | 7 | 1 | 1 | 19 | 0.240 | 0.970 | 0.449 |
| SS | Jason | Wichert | 28 | 72 | 4 | 14 | 2 | 0 | 0 | 6 | 3 | 0 | 18 | 0 | 0 | 2 | 1 | 0 | 2 | 0.194 | 0.895 | 0.222 |
| SS | Chris | Grube | 10 | 28 | 0 | 1 | 0 | 0 | 0 | 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0.036 | 1.000 | 0.036 |
+ -------- + --------------- + -------------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
For a little further cleanup, replacing the first_name and last_name lines with another concat:
concat(plr.first_name, ' ', plr.last_name) as name,
Gives you this:
Code:
+ -------- + --------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
| pos | name | g | ab | r | h | 2B | 3B | hr | rbi | bb | ibb | k | hpb | sh | sf | sb | cs | gdp | ba | obp | slg |
+ -------- + --------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
| 1B | Justin Richens | 63 | 231 | 34 | 71 | 17 | 2 | 9 | 26 | 41 | 3 | 14 | 6 | 0 | 5 | 0 | 0 | 6 | 0.307 | 0.959 | 0.515 |
| 1B | Jose Ayala | 51 | 193 | 24 | 55 | 9 | 0 | 5 | 23 | 13 | 2 | 34 | 1 | 0 | 4 | 2 | 0 | 10 | 0.285 | 0.945 | 0.409 |
| 1B | John Chapman | 60 | 138 | 13 | 31 | 6 | 0 | 3 | 14 | 26 | 1 | 16 | 1 | 0 | 2 | 0 | 0 | 0 | 0.225 | 0.967 | 0.333 |
| 1B | Bobby Berg | 12 | 23 | 7 | 8 | 2 | 0 | 2 | 8 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0.348 | 1.000 | 0.696 |
| 2B | Wing-fung Yi | 105 | 383 | 44 | 117 | 17 | 6 | 6 | 45 | 67 | 5 | 29 | 2 | 0 | 3 | 12 | 15 | 4 | 0.305 | 0.984 | 0.428 |
| 2B | Elijah Patton | 38 | 92 | 10 | 19 | 4 | 0 | 0 | 12 | 9 | 3 | 13 | 0 | 0 | 2 | 0 | 1 | 5 | 0.207 | 0.933 | 0.250 |
| 2B | Chris Eckert | 16 | 33 | 4 | 7 | 1 | 0 | 0 | 3 | 4 | 2 | 7 | 0 | 0 | 0 | 0 | 0 | 2 | 0.212 | 1.000 | 0.242 |
| 2B | Nate Lieb | 3 | 9 | 1 | 2 | 0 | 0 | 1 | 2 | 2 | 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0.222 | 1.000 | 0.556 |
| 3B | Tom Weiss | 128 | 423 | 62 | 115 | 12 | 2 | 17 | 69 | 60 | 3 | 38 | 7 | 1 | 3 | 0 | 0 | 8 | 0.272 | 0.978 | 0.430 |
| 3B | David Salinas | 49 | 169 | 14 | 36 | 5 | 0 | 1 | 18 | 15 | 0 | 12 | 1 | 0 | 2 | 2 | 2 | 8 | 0.213 | 0.963 | 0.260 |
| SS | Ty Stover | 146 | 530 | 85 | 127 | 24 | 6 | 25 | 92 | 96 | 1 | 105 | 3 | 0 | 7 | 1 | 1 | 19 | 0.240 | 0.970 | 0.449 |
| SS | Jason Wichert | 28 | 72 | 4 | 14 | 2 | 0 | 0 | 6 | 3 | 0 | 18 | 0 | 0 | 2 | 1 | 0 | 2 | 0.194 | 0.895 | 0.222 |
| SS | Chris Grube | 10 | 28 | 0 | 1 | 0 | 0 | 0 | 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0.036 | 1.000 | 0.036 |
+ -------- + --------- + ------ + ------- + ------ + ------ + ------- + ------- + ------- + -------- + ------- + -------- + ------ + -------- + ------- + ------- + ------- + ------- + -------- + ------- + -------- + -------- +
Welp, that didn't work! I fiddled around a little with trying to cast the "name" field to, for instance, a char(35) type but it just does not want to not truncate the values for this report. As I suspect this is regarded as gravy by the developers and not really a thing you're supposed to use unto itself, I'll let it slide, I guess!