SQLite newbie question

JamesC · Jul 24, 2008

I am using an SQLite database to store chess game data from a pgn file. I have a table ("Gamestable") in which each record stores data for one game. The data includes "Event", the "Eventdate" and the name of the "White" player. I wish to summarise the table by unique event (that is event||eventdate), giving for each unique event the number of different white players and the number of games.

The way I have managed to do this is by creating two temporary tables to find the number of players for each event. The code looks something like this:

CREATE TABLE eview (event1 CHAR(20), Name CHAR(30))

INSERT INTO eview (event1, Name) Select Event||Eventdate as event1, White from Gamestable group by event1, Name

CREATE TABLE eview2 (event1 CHAR(20), NrP SMALLINT)

INSERT INTO eview2 (event1, NrP) Select event1, count() as NrP from eview group by event1

CREATE TABLE summaryevents (Event CHAR(20), NrPlayers SMALLINT, NrGames SMALLINT, EventDate CHAR(10), Qevent CHAR(30))

INSERT INTO summaryevents (Event, NrPlayers, NrGames, EventDate, Qevent) Select Event, NrP, count() as NrGames, EventDate, Event||Eventdate As Qevent from Gamestable, eview2 where event1=Event||Eventdate group by Qevent

DROP TABLE eview

DROP TABLE eview2

It works, but I'm sure there's a much better and faster way of doing it.

Any suggestions would be appreciated!

Thanks

James

JamesC · Aug 1, 2008

Problem Solved

The rather simple solution is to use the DISTINCT keyword. The original SQL can be replaced with:

CREATE TABLE summaryevents (Event CHAR(20), NrPlayers SMALLINT, NrGames SMALLINT, EventDate CHAR(10), Qevent CHAR(30))

INSERT INTO summaryevents (Event, NrPlayers, NrGames, EventDate, Qevent) Select Event, Count(DISTINCT White) as NrP, count() as NrGames, EventDate, Event||Eventdate As Qevent from Gamestable group by Qevent

Useful to know, and about 100 times faster! :sign0188:

James

SQLite newbie question

JamesC

Member

JamesC

Member

SQLite newbie question

JamesC

Member

JamesC

Member

Privacy & Transparency

Privacy & Transparency