Hi,
First let me say that I am new to sqlite. My situation is this, I have two tables in an sqlite 3.0.8 database. One table (Master) is a list of
biographical data about baseball players: firstname, lastname, hometown,
etc. The second table (Batting) is comprised of each players stats: hits,
homeruns, etc. There is one row of stats for each player for each year they played. Each row in both databases has a "playerID".
I am trying to do a query that prints out the last name of each player and the total number of homeruns they hit in their career for players
that hit more than 500 in their career.
The query I used is this:
SELECT master.nameLast, sum(batting.hr) FROM master,batting WHERE master.playerid = batting.playerid GROUP BY master.nameLast, batting.playerid HAVING sum(batting.hr) > 500 ORDER BY sum(batting.hr) DESC;
In both postgresql and Microsoft Access 2000 this query takes 2 and 3 seconds to execute respectively. In sqlite3 this query took 34 minutes
and 10 seconds. sqlite3 and postgresql are on the same machine.
This query below quickly prints out the last names of every player who has hit 500 or more career homeruns, but I can't figure out how to get it to print their career total.
SELECT master.nameLast FROM master WHERE master.playerID IN (SELECT batting.playerID FROM batting GROUP BY batting.playerid HAVING sum(batting.hr) > 500);
So in summary, why is the first query so slow and how can I modify the second query to do what I am looking for.
Thanks, Angelo