Aggregate function fix for MySQL 5.7

2 files changed, 8 insertions, 11 deletions

diff --git a/charts.py b/charts.py
index 6125686..d0511ab 100644
--- a/charts.py
+++ b/charts.py
@@ -26,7 +26,6 @@ def fetch_raw():
     GROUP BY _driver
     ) r ON r.ranking=rankings.ranking AND r._driver=rankings._driver
     JOIN drivers ON rankings._driver = drivers.id
-    GROUP BY rankings._driver
     ORDER BY rankings.ranking DESC
     LIMIT 0,20
     ''',
@@ -42,7 +41,7 @@ def fetch_raw():
     SELECT MAX(mr.max_ranking) FROM max_date_rankings mr
     WHERE mr.max_rank_date < max_date_rankings.max_rank_date
     )
-    ''',                
+    ''',
     'top-exit-rankings': '''
     SELECT drivers.driver,
     rankings.ranking,
@@ -106,7 +105,7 @@ def fetch_raw():
         for result in connection.execute(text(query)):
             csv.writerow(result)
 
-def fetch_decades():            
+def fetch_decades():
     for decade in range(1950, 2020, 5):
         drivers = []
         for year in range(decade, decade + 5):
@@ -124,14 +123,14 @@ def fetch_decades():
             LIMIT 0,3)
             '''), year=year):
                 drivers.append(driver_id[0])
-        rankings = connection.execute(text("SELECT rankings.id, rankings.rank_date, MAX(rankings.ranking), rankings._driver, drivers.* FROM rankings JOIN drivers ON drivers.id = rankings._driver WHERE rank_date >= :date_from AND rank_date <= :date_to AND _driver IN :drivers GROUP BY rankings._driver, rankings.rank_date"), date_from=str(decade)+'-01-01', date_to=str(decade+4)+'-12-31', drivers=drivers)
+        rankings = connection.execute(text("SELECT rankings.rank_date, MAX(rankings.ranking), rankings._driver, drivers.* FROM rankings JOIN drivers ON drivers.id = rankings._driver WHERE rank_date >= :date_from AND rank_date <= :date_to AND _driver IN :drivers GROUP BY rankings._driver, rankings.rank_date"), date_from=str(decade)+'-01-01', date_to=str(decade+4)+'-12-31', drivers=drivers)
         csv = unicodecsv.writer(open('charts/' + str(decade) + 's.csv', 'w'))
         tree = lambda: collections.defaultdict(tree)
         table = tree()
         dates = set()
         for row in rankings:
-            dates.add(str(row[1]))
-            table[row[5]][str(row[1])] = row[2]
+            dates.add(str(row[0]))
+            table[row[4]][str(row[0])] = row[1]
         dates = list(dates)
         dates.append('')
         dates = sorted(dates)
@@ -172,4 +171,3 @@ elif sys.argv[1] == 'all':
     fetch_raw()
     fetch_decades()
     fetch_rolling()
-
diff --git a/sql/views.sql b/sql/views.sql
index b9b597c..6f5222f 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -15,7 +15,8 @@ CREATE VIEW driver_seasons_count AS
 DROP VIEW IF EXISTS driver_yearly_rankings;
 
 CREATE VIEW driver_yearly_rankings AS
-	SELECT MAX(rankings.ranking) max_ranking, AVG(rankings.ranking) avg_ranking, MIN(rankings.ranking) min_ranking, YEAR(rankings.rank_date) date, COUNT(rankings.id) count, championship.position, rankings._driver
+	SELECT MAX(rankings.ranking) max_ranking, AVG(rankings.ranking) avg_ranking, MIN(rankings.ranking) min_ranking,
+           YEAR(rankings.rank_date) date, COUNT(rankings.id) count, MIN(championship.position) position, rankings._driver
 	FROM rankings
 	LEFT JOIN championship ON rankings._driver = championship._driver AND YEAR(rankings.rank_date) = championship.year
 	GROUP BY YEAR(rankings.rank_date), rankings._driver;
@@ -59,6 +60,4 @@ CREATE VIEW top_peak_rankings AS
 	FROM rankings
 		JOIN top_yearly_rankings ON YEAR(rankings.rank_date) = top_yearly_rankings.date AND rankings.ranking = top_yearly_rankings.peak
 		JOIN drivers ON rankings._driver = drivers.id
-		JOIN driver_yearly_rankings ON rankings._driver = driver_yearly_rankings._driver AND top_yearly_rankings.date = driver_yearly_rankings.date
-	GROUP BY top_yearly_rankings.date;
-
+		JOIN driver_yearly_rankings ON rankings._driver = driver_yearly_rankings._driver AND top_yearly_rankings.date = driver_yearly_rankings.date;