Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Finding Data Based on Contents of Multiple Columns

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
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

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