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:

    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?

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]

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may