sql-如何在与groupby列进行内部联接后获得min、max、ave

Question

我试图在group by之后的一列中基于另一列获得最小值、最大值和平均值。

从我在这里看到的：SQL Get MIN((和MAX((与INNER JOIN

我的语法是正确的，所以我不确定问题出在哪里。

但我得到了这个错误：

一次只能执行一条语句。

代码为：

import sqlite3
import pandas as pd
battles=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/battles.csv')
character_deaths=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-deaths.csv')
character_predictions=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-predictions.csv')
character_deaths.loc[:,'Book_of_Death']=character_deaths.loc[:,'Book of Death']
cnx = sqlite3.connect('pythonsqlite.db')
battles.to_sql(name='battles', con=cnx, if_exists='replace')
character_deaths.to_sql(name='character_deaths', con=cnx, if_exists='replace')
character_predictions.to_sql(name='character_predictions', con=cnx, if_exists='replace')

#Edit the query
qry="""
--<<<write your query below this line>>>
SELECT MIN(character_predictions.age) as 
min_age,MAX(character_predictions.age) as 
max_age,AVG(character_predictions.age) as ave_age
FROM character_predictions
INNER JOIN character_deaths ON 
character_predictions.name = character_deaths.Name;
GROUP BY character_deaths.Book_of_Death
"""
pd.read_sql(qry, con=cnx)

我想得到的是每本书的"年龄"列的平均值、最小值、最大值(这就是我做group by.的原因(

Answer 1

删除联接条件末尾的分号：

INNER JOIN character_deaths ON  
character_predictions.name = character_deaths.Name; <-- REMOVE THIS CHARACTER  
GROUP BY character_deaths.Book_of_Death