CODEWITHSUNDEEP
sqlt-sql
wafw1971
wafw1971

Reputation: 361

Updating a Dataset to add calculated fields

I have a dataset see below:

SELECT jockey.jockey_skey
, raceresults.place 
FROM   jockey 
       INNER JOIN runnersandriders 
               ON jockey.jockey_skey = runnersandriders.jockey_skey 
       INNER JOIN horse 
               ON runnersandriders.horse_skey = horse.horse_skey 
       INNER JOIN raceresults 
               ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey, 
          raceresults.place 
ORDER  BY jockey.jockey_skey

This provides me with:

Jockey_Skey    Place
1              01
1              04
2              03
2              04
3              02
3              02

What I want to do is count how many races a Jockey has ran and the list where they have come.

So Jockey 1, Raced 2 times, Come 1st once and 4th once so on so forth but for the life of me I cannot remember how to do it.

Upvotes: 0

Views: 42

Answers (1)

GarethD
GarethD

Reputation: 69769

You could use something like this to get the results for each jockey in one row:

SELECT  jockey.jockey_skey,
        TotalRaces = COUNT(*),
        [1sts] = COUNT(CASE WHEN raceresults.place = '01' THEN 1 END),
        [2nds] = COUNT(CASE WHEN raceresults.place = '02' THEN 1 END),
        [3rds] = COUNT(CASE WHEN raceresults.place = '03' THEN 1 END),
        [4ths] = COUNT(CASE WHEN raceresults.place = '04' THEN 1 END),
        [5ths] = COUNT(CASE WHEN raceresults.place = '05' THEN 1 END),
        [6ths] = COUNT(CASE WHEN raceresults.place = '06' THEN 1 END),
        [7ths] = COUNT(CASE WHEN raceresults.place = '07' THEN 1 END),
        [8ths] = COUNT(CASE WHEN raceresults.place = '08' THEN 1 END),
        -- etc
        [NonRunner] = COUNT(CASE WHEN raceresults.place = 'NR' THEN 1 END),
        [Fell] = COUNT(CASE WHEN raceresults.place = 'F' THEN 1 END),
        [PulledUp] = COUNT(CASE WHEN raceresults.place = 'PU' THEN 1 END),
        [Unseated] = COUNT(CASE WHEN raceresults.place = 'U' THEN 1 END),
        [Refused] = COUNT(CASE WHEN raceresults.place = 'R' THEN 1 END),
        [BroughtDown] = COUNT(CASE WHEN raceresults.place = 'B' THEN 1 END)
FROM    jockey 
        INNER JOIN runnersandriders 
            ON jockey.jockey_skey = runnersandriders.jockey_skey 
        INNER JOIN horse 
            ON runnersandriders.horse_skey = horse.horse_skey 
        INNER JOIN raceresults 
            ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey
ORDER  BY jockey.jockey_skey

Simplified Example on SQL Fiddle

ALternatively you could use WITH ROLLUP to get an additional row with totals:

SELECT  jockey.jockey_skey,
        raceresults.place,
        [CountOfResult] = COUNT(*)
FROM    jockey 
        INNER JOIN runnersandriders 
            ON jockey.jockey_skey = runnersandriders.jockey_skey 
        INNER JOIN horse 
            ON runnersandriders.horse_skey = horse.horse_skey 
        INNER JOIN raceresults 
            ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey, raceresults.place
WITH ROLLUP
ORDER  BY jockey.jockey_skey, raceresults.place;

Where NULL values represent totals

Simplified Example on SQL Fiddle

Upvotes: 1

Related Questions