Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Finding Data Based on Contents of Multiple Columns

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:

        (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?

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
        SELECT place, COUNT(*), 'place'
            FROM Ponies
            GROUP BY place
        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

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date