SQL Group BY using strings in new columns

Question

I have a database of race results. Each race event has multiple classes.

events table:

event_id | event_date
---------+------------
1        | 11/5/14
2        | 11/12/14
3        | 11/19/14

results table:

result_event | name          | class | position
-------------+---------------+-------+-----------
1            | Jason Smith   | 40    | 1
1            | David Johnson | 40    | 2
1            | Randy White   | 30    | 1
1            | Billy Hansen  | 30    | 2 
2            | Wally Mann    | 40    | 1
2            | Shawn Little  | 40    | 2
2            | Eric Davis    | 30    | 1
2            | Tom Handy     | 30    | 2

I want to create a summary table that lists the Event Date and the winners of each class.

Like this:

Event Date  | Class 40 Winner | Class 30 Winner
------------+-----------------+------------------
11/5/14     | Jason Smith     | Randy White
11/12/14    | Wally Mann      | Eric Davis

What query would I need so that I can create a GROUP BY event_id and list winners in separate columns?

"I've tried and tried," - Please show what you've tried. Correcting your attempt may be easier than writing a query from scratch, and it may be more informational for you to learn what exactly you did wrong. — GolezTrol
– GolezTrol, Commented Jun 23, 2015 at 20:32

Mureinik · Accepted Answer · 2015-06-25 05:38:29Z

1

You can join the events table on two queries from the results table, one for each class:

SELECT    event_data, class_40_winner, class_30_winner
FROM      events e
LEFT JOIN (SELECT result_event, name AS class_40_winner
           FROM   results 
           WHERE  class = 40 AND position = 1) c40 ON e.id = c40.result_event
LEFT JOIN (SELECT result_event, name AS class_30_winner
           FROM   results 
           WHERE  class = 30 AND position = 1) c30 ON e.id = c30.result_event

edited Jun 25, 2015 at 5:38

answered Jun 23, 2015 at 20:37

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RichieW13 Over a year ago

Yes, this did it. Thanks! Is there a functional reason that you give table "events" an alias of "e", or do you do that just to save keystrokes when you need to refer to the table later in the query?

Mureinik Over a year ago

events doesn't really need an alias in this case, it's just shorter to write every time, as you suspected.

Rick James Over a year ago

LEFT JOIN ( SELECT ... ) does not optimize well. See follow on

shA.t · Accepted Answer · 2017-01-14 09:15:19Z

0

You are querying like pivoting data, so I suggest you to use a query like this:

select event_date
    , max(case when r.class = 40 then name end) `Class 40 Winner`
    , max(case when r.class = 30 then name end) `Class 30 Winner`
from events e
left join results r on e.event_id = r.result_event and r.position = 1
group by event_date;

[SQL Fiddle Demo]

answered Jan 14, 2017 at 9:15

shA.t

17k5 gold badges59 silver badges121 bronze badges

Collectives™ on Stack Overflow

SQL Group BY using strings in new columns

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related