Question:
Joe Celko’s Playing the Ponies Puzzle]

You have just become the database manager for Eddie Franco “the Doctor” Coddetti. He keeps records on horse races for statistical purposes and his basic table looks like this:

    CREATE TABLE Ponies        (track CHAR(3) NOT NULL,        racedate DATE NOT NULL,        race INTEGER NOT NULL,        win CHAR(30) NOT NULL,        place CHAR(30) NOT NULL,        show CHAR(30) NOT NULL,        PRIMARY KEY (track, date, race));

The track column is the name of track where the race was held, racedate is when it was held, race is the number of the each race, and the other three columns are the names of the horse that won, placed, or showed for that race.

The Doc comes to you one day and he wants to know how many times each horse was in the money. What SQL query do you write for this?

Answer:
The phrase “in the money” means that the horse won, placed or showed in a race ? we don’t care which. The first step is to build a VIEW with the aggregate information, thus:

    CREATE VIEW InMoney (horse, tally, position) AS        SELECT win, COUNT(*), ‘win’            FROM Ponies            GROUP BY win        UNION        SELECT place, COUNT(*), ‘place’            FROM Ponies            GROUP BY place        UNION        SELECT show, COUNT(*), ‘show’            FROM Ponies            GROUP BY show;

Now use that view to get the final summary:

    SELECT horse, SUM(tally)        FROM InMoney        GROUP BY horse;

The reason for putting those string constants on the side is so that if Doc Coddetti wants to know how many times each horse finished in each position, you can just change the query to:

    SELECT horse, position, SUM(tally)        FROM InMoney        GROUP BY horse, position;

Puzzle provided courtesy of:
Joe Celko
[email protected]