bdilday · August 24, 2016 21:10
diff --git a/result.csv b/result.csv
diff --git a/sql_example.sql b/sql_example.sql
 select
 year_id year,
 home_away,
 pit_id withPitcher,
 sum(iout) ipouts,
 sum(bip) bip,
 sum(icf) AshburnPO
 from
 (select game_id, if(bat_home_id=1, 'away', 'home') home_away, year_id, pit_id, po1_fld_cd=8 as icf, event_cd<=3 iout, (event_cd=2 or (event_cd>=18 and event_cd<=22) or (event_cd=23 and fld_cd>0)) as bip  from events where year_id<1964 and POS8_FLD_ID = 'ashbr101' ) a group by year_id, pit_id, home_away order by year_id, withPitcher;
year	home_away	withPitcher	IPouts	BIP	AshburnPO
1950	away	boroh101	9	8	0
1950	home	boroh101	3	5	0
1950	away	britj102	5	6	0
1950	home	britj102	6	5	1
1950	away	candm101	25	34	2
1950	home	candm101	31	38	5
1950	away	churb101	162	181	24
1950	home	churb101	206	236	24
1950	away	donnb101	37	44	3
1950	home	donnb101	10	11	2
1950	away	heink101	161	195	20
1950	home	heink101	141	168	20
1950	away	johnk101	74	91	8
1950	home	johnk101	94	105	7
1950	away	konsj101	183	206	21
1950	home	konsj101	207	225	21
1950	away	meyer101	178	223	24
1950	home	meyer101	236	291	24
1950	away	millb103	171	221	14
1950	home	millb103	211	281	20
1950	home	ridzs101	8	8	3
1950	away	rober102	319	373	33
1950	home	rober102	433	490	42
1950	away	simmc101	244	270	25
1950	home	simmc101	231	249	28
1950	away	stufp101	3	4	0
1950	home	stufp101	12	13	0
1950	away	thomj101	8	7	2
1951	away	britj102	3	6	0
1951	home	britj102	9	8	1
1951	away	candm101	33	45	7
1951	home	candm101	41	44	4
1951	away	churb101	255	296	36
1951	home	churb101	339	416	42
1951	away	crisl101	24	37	2
1951	home	crisl101	32	36	4
1951	away	drewk101	34	42	1
1951	home	drewk101	26	32	1
1951	away	hansa101	36	42	2
1951	home	hansa101	42	50	6
1951	away	heink101	54	59	8
1951	home	heink101	225	270	34
1951	away	johnk101	112	127	13
1951	home	johnk101	88	98	10
1951	away	jordn101	39	51	8
1951	home	jordn101	26	26	1
1951	away	konsj101	112	142	14
1951	home	konsj101	158	210	22
1951	away	meyer101	207	248	24
1951	home	meyer101	193	232	28
1951	away	millb103	27	40	8
1951	home	millb103	43	62	8
1951	home	possl101	6	5	0
1951	away	rober102	385	443	53
1951	home	rober102	427	498	69
1951	away	thomj101	96	111	6
1951	home	thomj101	173	180	23
1952	away	churb101	5	11	0
1952	home	churb101	9	11	0
1952	away	drewk101	274	332	29
1952	home	drewk101	310	369	28
1952	away	fox-h101	111	133	13
1952	home	fox-h101	38	54	3
1952	away	hansa101	52	65	7
1952	home	hansa101	99	127	18
1952	away	heink101	48	59	5
1952	home	heink101	57	68	5
1952	away	konsj101	106	140	11
1952	home	konsj101	87	112	6
1952	away	meyer101	223	269	25
1952	home	meyer101	345	435	44
1952	away	millb103	6	4	0
1952	home	millb103	18	29	3
1952	away	petek101	14	9	1
1952	home	petek101	6	6	1
1952	away	possl101	6	6	2
1952	home	possl101	9	8	3
1952	away	ridzs101	98	117	10
1952	home	ridzs101	130	137	17
1952	away	rober102	391	461	44
1952	home	rober102	400	449	49
1952	away	simmc101	197	220	21
1952	home	simmc101	230	229	22
1952	away	stufp101	15	19	0
1952	home	stufp101	3	2	0
1953	away	drewk101	160	194	13
1953	home	drewk101	290	357	43
1953	away	hansa101	69	94	8
1953	home	hansa101	58	66	8
1953	away	kippt101	35	47	2
1953	home	kippt101	80	105	6
1953	away	konsj101	128	177	21
1953	home	konsj101	253	324	37
1953	away	lindj101	66	72	9
1953	home	lindj101	1	4	0
1953	away	millb103	249	290	28
1953	home	millb103	165	220	19
1953	away	petek101	32	36	5
1953	home	petek101	38	40	3
1953	away	ridzs101	145	168	25
1953	home	ridzs101	147	167	23
1953	away	rober102	317	356	40
1953	home	rober102	466	516	62
1953	away	simmc101	166	169	19
1953	home	simmc101	323	376	39
1953	home	stufp101	0	0	0
1954	away	dickm101	251	301	31
1954	home	dickm101	319	417	48
1954	away	drewk101	25	31	2
1954	home	drewk101	17	22	2
1954	away	greeb103	67	79	5
1954	home	greeb103	37	43	8
1954	away	kippt101	16	29	2
1954	home	kippt101	15	17	2
1954	away	konsj101	72	96	10
1954	home	konsj101	54	76	6
1954	away	millb103	140	185	18
1954	home	millb103	248	329	36
1954	away	mrozr101	54	65	4
1954	home	mrozr101	74	81	7
1954	away	pensp101	11	14	2
1954	home	pensp101	34	44	4
1954	away	ridzs101	108	126	10
1954	home	ridzs101	106	112	15
1954	away	rober102	393	443	38
1954	home	rober102	493	520	66
1954	away	simmc101	226	264	36
1954	home	simmc101	396	463	50
1954	away	wehmh101	211	259	34
1954	home	wehmh101	142	155	17
1955	away	coled103	15	13	2
1955	home	coled103	22	26	4
1955	away	dickm101	259	299	23
1955	home	dickm101	268	313	42
1955	away	kippt101	30	44	1
1955	home	kippt101	53	66	9
1955	away	kuzab101	52	68	4
1955	home	kuzab101	38	48	7
1955	away	lovel102	22	23	5
1955	home	lovel102	23	24	2
1955	away	meyej101	137	128	11
1955	home	meyej101	145	127	13
1955	away	millb103	122	146	13
1955	home	millb103	111	139	11
1955	away	mrozr101	31	43	4
1955	home	mrozr101	50	59	5
1955	away	negrr101	93	112	13
1955	home	negrr101	109	121	18
1955	away	owenj101	4	4	0
1955	away	ridzs101	6	9	0
1955	away	rober102	359	419	33
1955	home	rober102	395	436	37
1955	away	rogos101	40	53	3
1955	home	rogos101	138	160	23
1955	away	simmc101	196	247	17
1955	home	simmc101	149	173	23
1955	away	sprij102	6	4	0
1955	home	sprij102	2	2	0
1955	away	wehmh101	225	257	31
1955	home	wehmh101	227	268	30
1956	away	dickm101	39	56	7
1956	home	dickm101	20	27	3
1956	away	farrt101	11	17	0
1956	away	flowb101	53	57	6
1956	home	flowb101	58	80	11
1956	away	haddh101	250	265	37
1956	home	haddh101	345	366	42
1956	away	hamng102	24	30	5
1956	away	lipea101	29	31	3
1956	home	lipea101	3	0	0
1956	away	meyej101	111	113	14
1956	home	meyej101	160	170	21
1956	away	millb103	163	183	17
1956	home	millb103	181	223	22
1956	away	mills101	149	167	16
1956	home	mills101	150	170	9
1956	away	negrr101	113	127	14
1956	home	negrr101	67	83	13
1956	away	owenj101	25	34	5
1956	home	owenj101	55	58	6
1956	away	pilld101	53	70	6
1956	home	pilld101	15	20	3
1956	away	rober102	412	511	63
1956	home	rober102	434	475	55
1956	away	rogos101	167	201	18
1956	home	rogos101	143	166	23
1956	away	rossb101	10	9	0
1956	away	sanfj101	7	6	1
1956	home	sanfj101	26	28	3
1956	away	simmc101	246	277	28
1956	home	simmc101	298	360	44
1956	away	wehmh101	30	39	5
1956	home	wehmh101	26	28	1
1957	away	cardd101	117	132	15
1957	home	cardd101	239	238	25
1957	away	farrt101	99	105	6
1957	home	farrt101	130	155	11
1957	away	hackw102	133	147	23
1957	home	hackw102	79	95	7
1957	away	haddh101	250	267	21
1957	home	haddh101	245	260	44
1957	home	hamng102	3	3	0
1957	away	hearj102	122	136	5
1957	home	hearj102	84	105	13
1957	away	meyej101	60	61	5
1957	home	meyej101	37	42	5
1957	away	millb103	78	87	16
1957	home	millb103	94	133	18
1957	away	mores101	79	89	10
1957	home	mores101	64	78	13
1957	away	qualt101	15	22	0
1957	away	rober102	351	419	64
1957	home	rober102	373	399	59
1957	away	rogos101	9	13	0
1957	home	rogos101	15	21	5
1957	away	sanfj101	358	370	40
1957	home	sanfj101	328	312	31
1957	away	simmc101	295	357	37
1957	home	simmc101	308	372	26
1958	away	andej102	23	32	1
1958	home	andej102	22	27	1
1958	away	cardd101	157	175	21
1958	home	cardd101	150	145	16
1958	home	conlb101	25	35	0
1958	away	ericd101	3	4	0
1958	home	ericd101	31	30	3
1958	away	farrt101	132	129	9
1958	home	farrt101	133	151	14
1958	away	grayj101	25	24	1
1958	home	grayj101	20	19	5
1958	away	hackw102	31	42	4
1958	home	hackw102	12	20	2
1958	away	hearj102	60	74	8
1958	home	hearj102	151	195	13
1958	away	lipea101	5	9	2
1958	home	lipea101	6	7	0
1958	home	masoh101	15	19	3
1958	away	meyej101	87	101	14
1958	home	meyej101	170	142	17
1958	away	millb103	22	32	6
1958	home	millb103	36	46	7
1958	away	mores101	104	138	11
1958	home	mores101	153	187	20
1958	away	owenj101	21	22	1
1958	home	qualt101	6	9	0
1958	away	rober102	377	440	49
1958	home	rober102	378	430	63
1958	away	sanfj101	228	271	29
1958	home	sanfj101	295	337	39
1958	away	sempr101	289	334	33
1958	home	sempr101	299	356	39
1958	away	simmc101	281	347	38
1958	home	simmc101	167	216	24
1959	away	bowmb102	11	14	1
1959	home	bowmb102	1	1	0
1959	away	cardd101	196	197	14
1959	home	cardd101	179	191	21
1959	away	conlg101	250	281	26
1959	home	conlg101	266	293	24
1959	away	farrt101	79	97	8
1959	home	farrt101	79	88	2
1959	away	gomer101	116	141	15
1959	home	gomer101	87	107	8
1959	away	hearj102	24	34	4
1959	home	hearj102	8	10	1
1959	away	keege101	25	38	4
1959	home	keege101	1	3	0
1959	away	meyej101	103	103	10
1959	home	meyej101	140	143	17
1959	away	mores101	6	6	0
1959	home	mores101	23	28	3
1959	away	owenj101	276	315	25
1959	home	owenj101	255	273	25
1959	away	philt101	73	78	4
1959	home	philt101	96	125	10
1959	away	rober102	326	383	41
1959	home	rober102	391	445	38
1959	away	robih101	112	145	8
1959	home	robih101	76	86	8
1959	away	rodrf101	6	8	0
1959	away	schra101	19	22	2
1959	home	schra101	5	9	1
1959	away	sempr101	87	110	9
1959	home	sempr101	187	215	24
1959	away	shorc102	11	12	1
1959	home	shorc102	30	38	2
1959	away	simmc101	8	11	1
1959	home	simmc101	13	23	2
1960	away	andeb102	224	249	21
1960	home	andeb102	147	166	15
1960	away	cardd101	92	94	7
1960	home	cardd101	134	137	11
1960	away	cecca101	18	20	0
1960	home	cecca101	19	24	2
1960	away	drabm101	35	52	5
1960	home	drabm101	39	53	2
1960	away	drotd101	28	34	4
1960	home	drotd101	88	102	8
1960	away	ellsd101	68	83	2
1960	home	ellsd101	201	226	11
1960	away	elstd101	136	128	14
1960	home	elstd101	116	130	9
1960	away	freem101	52	55	5
1960	home	freem101	72	76	11
1960	away	goetj101	10	11	1
1960	home	goetj101	8	9	0
1960	away	hobbg101	227	265	27
1960	home	hobbg101	173	206	20
1960	away	johnb107	41	52	5
1960	home	johnb107	33	52	3
1960	away	mores101	87	112	8
1960	home	mores101	132	138	10
1960	away	schaj105	28	27	0
1960	home	schaj105	33	34	2
1960	away	schra101	8	8	2
1960	away	wrigm101	9	11	1
1960	home	wrigm101	17	23	3
1961	away	andeb102	88	105	7
1961	home	andeb102	71	80	1
1961	away	brewj102	91	108	7
1961	home	brewj102	48	63	4
1961	away	burwd101	11	14	1
1961	home	burwd101	0	3	0
1961	away	cardd101	111	113	9
1961	home	cardd101	116	119	5
1961	away	curtj102	77	103	5
1961	home	curtj102	110	137	15
1961	away	drotd101	67	71	6
1961	home	drotd101	24	28	4
1961	away	ellsd101	104	134	8
1961	home	ellsd101	72	90	4
1961	away	elstd101	61	76	6
1961	home	elstd101	40	46	1
1961	away	hobbg101	71	77	7
1961	home	hobbg101	83	98	7
1961	away	schaj105	35	39	7
1961	home	schaj105	9	9	0
1961	away	schub101	42	49	1
1961	home	schub101	28	19	0
1961	away	wrigm101	16	27	0
1961	home	wrigm101	3	8	0
1962	away	andec101	80	101	10
1962	home	andec101	34	43	2
1962	away	crair101	100	136	12
1962	home	crair101	34	48	2
1962	away	davir101	41	44	3
1962	home	davir101	36	37	2
1962	away	hilld102	6	6	0
1962	home	hilld102	3	3	0
1962	away	hookj101	70	90	7
1962	home	hookj101	73	83	10
1962	away	huntw102	24	26	2
1962	home	huntw102	24	34	4
1962	away	jacka101	118	148	11
1962	home	jacka101	90	103	6
1962	away	jones102	3	3	0
1962	home	jones102	14	17	0
1962	away	labic101	3	5	1
1962	away	mackk101	38	54	3
1962	home	mackk101	32	30	3
1962	away	millb102	4	6	0
1962	home	millb102	23	35	3
1962	away	millb106	88	102	8
1962	home	millb106	64	67	2
1962	away	mizew101	30	35	10
1962	home	mizew101	7	4	0
1962	away	mofoh101	5	10	0
1962	home	mofoh101	14	24	0
1962	away	moorb102	55	70	5
1962	home	moorb102	41	52	4
	select
	year_id year,
	home_away,
	pit_id withPitcher,
	sum(iout) ipouts,
	sum(bip) bip,
	sum(icf) AshburnPO
	from
	(select game_id, if(bat_home_id=1, 'away', 'home') home_away, year_id, pit_id, po1_fld_cd=8 as icf, event_cd<=3 iout, (event_cd=2 or (event_cd>=18 and event_cd<=22) or (event_cd=23 and fld_cd>0)) as bip from events where year_id<1964 and POS8_FLD_ID = 'ashbr101' ) a group by year_id, pit_id, home_away order by year_id, withPitcher;