Protoball:AreaStats: Difference between revisions
Jump to navigation
Jump to search
(Borough Games within Year Range) |
No edit summary |
||
Line 1: | Line 1: | ||
== Borough | == Games per Borough in Year Range == | ||
<pre> | <pre> | ||
select Places.name borough, count(1) games | select Places.name borough, count(1) games | ||
from Ballgames join Places | from Ballgames | ||
join Places | |||
on MBRContains(area, coordinates_point) | |||
where level = 6 | |||
and year(Ballgames.date) BETWEEN 1842 AND 1870 | and year(Ballgames.date) BETWEEN 1842 AND 1870 | ||
group by Places.name | group by Places.name | ||
</pre> | </pre> | ||
=== Result === | === Example Result === | ||
<table class="nice"> | <table class="nice"> | ||
Line 38: | Line 39: | ||
</tr> | </tr> | ||
</table> | </table> | ||
== Clubs per Borough in Year Range == | |||
<pre> | |||
select Places.name borough, count(1) clubs | |||
from Clubs | |||
join Places | |||
on MBRContains(area, coordinates_point) | |||
where level = 6 | |||
and year(Clubs.date) BETWEEN 1842 AND 1870 | |||
group by Places.name | |||
</pre> | |||
== Games per Club in Year Range == | |||
<pre> | |||
select Places.name borough, Clubs.name, count(1) games | |||
from Clubs | |||
join Places | |||
on MBRContains(area, coordinates_point) | |||
join Ballgames | |||
on Ballgames.home_team = Clubs.name | |||
where level = 6 | |||
and year(Clubs.date) BETWEEN 1842 AND 1870 | |||
group by Places.name, Clubs.name | |||
</pre> | |||
== Average Number of Innings Played, Runs Scored Per Game == | |||
(I have to update the score meta-data, this might take some time) | |||
<pre> | |||
select AVG(innings) innings, AVG(home_score) home, AVG(away_score) away | |||
from Ballgames | |||
join Places | |||
on MBRContains(area, coordinates_point) | |||
where level = 6 | |||
and year(Ballgames.date) BETWEEN 1842 AND 1870 | |||
</pre> |
Latest revision as of 06:43, 9 August 2014
Games per Borough in Year Range
select Places.name borough, count(1) games from Ballgames join Places on MBRContains(area, coordinates_point) where level = 6 and year(Ballgames.date) BETWEEN 1842 AND 1870 group by Places.name
Example Result
borough | games |
---|---|
Kings County | 764 |
Nassau County | 1 |
New York County | 752 |
Queens County | 1113 |
Richmond County | 2 |
Clubs per Borough in Year Range
select Places.name borough, count(1) clubs from Clubs join Places on MBRContains(area, coordinates_point) where level = 6 and year(Clubs.date) BETWEEN 1842 AND 1870 group by Places.name
Games per Club in Year Range
select Places.name borough, Clubs.name, count(1) games from Clubs join Places on MBRContains(area, coordinates_point) join Ballgames on Ballgames.home_team = Clubs.name where level = 6 and year(Clubs.date) BETWEEN 1842 AND 1870 group by Places.name, Clubs.name
Average Number of Innings Played, Runs Scored Per Game
(I have to update the score meta-data, this might take some time)
select AVG(innings) innings, AVG(home_score) home, AVG(away_score) away from Ballgames join Places on MBRContains(area, coordinates_point) where level = 6 and year(Ballgames.date) BETWEEN 1842 AND 1870