Solved

Create a query with 3 SELECT and multiple CASE

Posted on 2013-11-08
11
212 Views
Last Modified: 2013-11-11
I want to lookup the number of birdies, pars and bogeys a player has for a golf tournament.

The SQL query below counts birdies or better, pars and bogeys or worse by comparing the score to the par of the hole.  

It's rather rudimentary and it does work but I feel it could be streamlined much better.

 SELECT
 
 CASE WHEN td.hole_1<course.hole_1 THEN 1 Else 0 End +
 CASE WHEN td.hole_2<course.hole_2 THEN 1 Else 0 End +
 CASE WHEN td.hole_3<course.hole_3 THEN 1 Else 0 End +
 CASE WHEN td.hole_4<course.hole_4 THEN 1 Else 0 End +
 CASE WHEN td.hole_5<course.hole_5 THEN 1 Else 0 End +
 CASE WHEN td.hole_6<course.hole_6 THEN 1 Else 0 End +
 CASE WHEN td.hole_7<course.hole_7 THEN 1 Else 0 End +
 CASE WHEN td.hole_8<course.hole_8 THEN 1 Else 0 End +
 CASE WHEN td.hole_9<course.hole_9 THEN 1 Else 0 End +
 CASE WHEN td.hole_10<course.hole_10 THEN 1 Else 0 End +
 CASE WHEN td.hole_11<course.hole_11 THEN 1 Else 0 End +
 CASE WHEN td.hole_12<course.hole_12 THEN 1 Else 0 End +
 CASE WHEN td.hole_13<course.hole_13 THEN 1 Else 0 End +
 CASE WHEN td.hole_14<course.hole_14 THEN 1 Else 0 End +
 CASE WHEN td.hole_15<course.hole_15 THEN 1 Else 0 End +
 CASE WHEN td.hole_16<course.hole_16 THEN 1 Else 0 End +
 CASE WHEN td.hole_17<course.hole_17 THEN 1 Else 0 End +
 CASE WHEN td.hole_18<course.hole_18 THEN 1 Else 0 End

 As countbirdies
 
 FROM tournaments td JOIN course_info course ON course.course=td.course WHERE course.type='par' And td.player_name LIKE '%%Player, Sample%%' And td.season='2014' And td.tournament='Spring Handicap' And course.course='Augusta National' And course.tee='black'
 
 UNION ALL
 
 SELECT
 
 CASE WHEN td.hole_1=course.hole_1 THEN 1 Else 0 End +
 CASE WHEN td.hole_2=course.hole_2 THEN 1 Else 0 End +
 CASE WHEN td.hole_3=course.hole_3 THEN 1 Else 0 End +
 CASE WHEN td.hole_4=course.hole_4 THEN 1 Else 0 End +
 CASE WHEN td.hole_5=course.hole_5 THEN 1 Else 0 End +
 CASE WHEN td.hole_6=course.hole_6 THEN 1 Else 0 End +
 CASE WHEN td.hole_7=course.hole_7 THEN 1 Else 0 End +
 CASE WHEN td.hole_8=course.hole_8 THEN 1 Else 0 End +
 CASE WHEN td.hole_9=course.hole_9 THEN 1 Else 0 End +
 CASE WHEN td.hole_10=course.hole_10 THEN 1 Else 0 End +
 CASE WHEN td.hole_11=course.hole_11 THEN 1 Else 0 End +
 CASE WHEN td.hole_12=course.hole_12 THEN 1 Else 0 End +
 CASE WHEN td.hole_13=course.hole_13 THEN 1 Else 0 End +
 CASE WHEN td.hole_14=course.hole_14 THEN 1 Else 0 End +
 CASE WHEN td.hole_15=course.hole_15 THEN 1 Else 0 End +
 CASE WHEN td.hole_16=course.hole_16 THEN 1 Else 0 End +
 CASE WHEN td.hole_17=course.hole_17 THEN 1 Else 0 End +
 CASE WHEN td.hole_18=course.hole_18 THEN 1 Else 0 End

 As countpars
 
 FROM tournaments td JOIN course_info course ON course.course=td.course WHERE course.type='par' And td.player_name LIKE '%%Player, Sample%%' And td.season='2014' And td.tournament='Spring Handicap' And course.course='Augusta National' And course.tee='black'
 
 UNION ALL
 
 SELECT
 
 CASE WHEN td.hole_1>course.hole_1 THEN 1 Else 0 End +
 CASE WHEN td.hole_2>course.hole_2 THEN 1 Else 0 End +
 CASE WHEN td.hole_3>course.hole_3 THEN 1 Else 0 End +
 CASE WHEN td.hole_4>course.hole_4 THEN 1 Else 0 End +
 CASE WHEN td.hole_5>course.hole_5 THEN 1 Else 0 End +
 CASE WHEN td.hole_6>course.hole_6 THEN 1 Else 0 End +
 CASE WHEN td.hole_7>course.hole_7 THEN 1 Else 0 End +
 CASE WHEN td.hole_8>course.hole_8 THEN 1 Else 0 End +
 CASE WHEN td.hole_9>course.hole_9 THEN 1 Else 0 End +
 CASE WHEN td.hole_10>course.hole_10 THEN 1 Else 0 End +
 CASE WHEN td.hole_11>course.hole_11 THEN 1 Else 0 End +
 CASE WHEN td.hole_12>course.hole_12 THEN 1 Else 0 End +
 CASE WHEN td.hole_13>course.hole_13 THEN 1 Else 0 End +
 CASE WHEN td.hole_14>course.hole_14 THEN 1 Else 0 End +
 CASE WHEN td.hole_15>course.hole_15 THEN 1 Else 0 End +
 CASE WHEN td.hole_16>course.hole_16 THEN 1 Else 0 End +
 CASE WHEN td.hole_17>course.hole_17 THEN 1 Else 0 End +
 CASE WHEN td.hole_18>course.hole_18 THEN 1 Else 0 End

 As countbogeys
 
 FROM tournaments td JOIN course_info course ON course.course=td.course WHERE course.type='par' And td.player_name LIKE '%%Player, Sample%%' And td.season='2014' And td.tournament='Spring Handicap' And course.course='Augusta National' And course.tee='black'

Open in new window

0
Comment
Question by:mcnuttlaw
11 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39635324
Can you provide some sample data to start?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39635335
In large part, the "repetitive structure" of your query is due to the de-normalized structure of your tables.

course hole_1 hole_2 hole_3 hole_4 ... hole_18
'augusta' 3, 3, 3, 3, ... 3 (or whatever)

instead of :

course hole_num hole_value
augusta, 1, 3
augusta, 2, 3
augusta, 3, 3
augusta, 4, 3
...
augusta, 18, 3

It's a pity you haven't provided the table definitions and sample data but will come back here once I have dummied-up something to work with.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39635382
Example result:
|           COURSE |    PLAYER_NAME | SEASON |      TOURNAMENT | BIRDIES | PARS | BOGEYS | COURSE_SCORE | COURSE_PAR |
|------------------|----------------|--------|-----------------|---------|------|--------|--------------|------------|
| Augusta National | Player, Sample |   2014 | Spring Handicap |       5 |    4 |      9 |           77 |         73 |

Open in new window

That result was produced by the following and this is what a query would look like of the score data was normalized:
SELECT
          course
        , player_name
        , season
        , tournament
        , count(CASE WHEN score < par THEN 1 END) AS birdies
        , count(CASE WHEN score = par THEN 1 END) AS pars
        , count(CASE WHEN score > par THEN 1 END) AS bogeys
        , sum(score)                              AS course_score
        , sum(par)                                AS course_par
FROM tournament_black_par_v

WHERE player_name LIKE '%%Player, Sample%%'
        AND season = '2014'
        AND tournament = 'Spring Handicap'
        AND course = 'Augusta National'

GROUP BY
          course
        , player_name
        , season
        , tournament
;

Open in new window

You may notice that I have used a view instead of your tables, that's because I "normalize" the data inside the view. To do this I created a simple table of 18 rows representing the 18 holes. Then by using a cross join and case expressions I can simulate a normalized data structure. Here is the code for the view:
create view tournament_black_par_v
AS
SELECT
          ci.course
        , player_name
        , season
        , tournament
        , h.hole
        , CASE WHEN h.hole = 1  THEN ci.hole_1
               WHEN h.hole = 2  THEN ci.hole_2
               WHEN h.hole = 3  THEN ci.hole_3
               WHEN h.hole = 4  THEN ci.hole_4
               WHEN h.hole = 5  THEN ci.hole_5
               WHEN h.hole = 6  THEN ci.hole_6
               WHEN h.hole = 7  THEN ci.hole_7
               WHEN h.hole = 8  THEN ci.hole_8
               WHEN h.hole = 9  THEN ci.hole_9
               WHEN h.hole = 10 THEN ci.hole_10
               WHEN h.hole = 11 THEN ci.hole_11
               WHEN h.hole = 12 THEN ci.hole_12
               WHEN h.hole = 13 THEN ci.hole_13
               WHEN h.hole = 14 THEN ci.hole_14
               WHEN h.hole = 15 THEN ci.hole_15
               WHEN h.hole = 16 THEN ci.hole_16
               WHEN h.hole = 17 THEN ci.hole_17
               WHEN h.hole = 18 THEN ci.hole_18
          END AS par
        , CASE WHEN h.hole = 1  THEN tp.hole_1
               WHEN h.hole = 2  THEN tp.hole_2
               WHEN h.hole = 3  THEN tp.hole_3
               WHEN h.hole = 4  THEN tp.hole_4
               WHEN h.hole = 5  THEN tp.hole_5
               WHEN h.hole = 6  THEN tp.hole_6
               WHEN h.hole = 7  THEN tp.hole_7
               WHEN h.hole = 8  THEN tp.hole_8
               WHEN h.hole = 9  THEN tp.hole_9
               WHEN h.hole = 10 THEN tp.hole_10
               WHEN h.hole = 11 THEN tp.hole_11
               WHEN h.hole = 12 THEN tp.hole_12
               WHEN h.hole = 13 THEN tp.hole_13
               WHEN h.hole = 14 THEN tp.hole_14
               WHEN h.hole = 15 THEN tp.hole_15
               WHEN h.hole = 16 THEN tp.hole_16
               WHEN h.hole = 17 THEN tp.hole_17
               WHEN h.hole = 18 THEN tp.hole_18
          END AS score
FROM course_info AS ci
INNER JOIN tournaments AS tp ON ci.course = tp.course
CROSS JOIN holes18 as h
WHERE ci.type = 'par'
        AND ci.tee = 'black'
;

Open in new window

As I don't really have all the facts before me you will see this view is specifically for black tee pars. The table "holes18" is truly simple:
CREATE TABLE holes18
	(`hole` int)
;
	
INSERT INTO holes18
	(`hole`)
VALUES
	(1),
	(2),
	(3),
	(4),
	(5),
	(6),
	(7),
	(8),
	(9),
	(10),
	(11),
	(12),
	(13),
	(14),
	(15),
	(16),
	(17),
	(18)
;

Open in new window

see the demo of this at: http://sqlfiddle.com/#!9/a7095/4
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:mcnuttlaw
ID: 39635391
Sorry, it didn't even dawn on me to provide sample data.

I've attached two files representing sample data from the two tables.

One table contains the hole-by-hole info for various courses and the other contains the hole-by-hole results for each tournament.
course-info.csv
tournaments.csv
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39635561
I'd still "unpivot" your table data to make it easier to calculate things, so if you don't like the idea of using views* you can just do it as a nested query, like so:
SELECT
          course
        , player_name
        , season
        , tournament
        , count(CASE WHEN score < par THEN 1 END) AS birdies
        , count(CASE WHEN score = par THEN 1 END) AS pars
        , count(CASE WHEN score > par THEN 1 END) AS bogeys
        , sum(score)                              AS course_score
        , sum(par)                                AS course_par
FROM (
      SELECT
                ci.course
              , player_name
              , season
              , tournament
              , h.hole
              , CASE WHEN h.hole = 1  THEN ci.hole_1
                     WHEN h.hole = 2  THEN ci.hole_2
                     WHEN h.hole = 3  THEN ci.hole_3
                     WHEN h.hole = 4  THEN ci.hole_4
                     WHEN h.hole = 5  THEN ci.hole_5
                     WHEN h.hole = 6  THEN ci.hole_6
                     WHEN h.hole = 7  THEN ci.hole_7
                     WHEN h.hole = 8  THEN ci.hole_8
                     WHEN h.hole = 9  THEN ci.hole_9
                     WHEN h.hole = 10 THEN ci.hole_10
                     WHEN h.hole = 11 THEN ci.hole_11
                     WHEN h.hole = 12 THEN ci.hole_12
                     WHEN h.hole = 13 THEN ci.hole_13
                     WHEN h.hole = 14 THEN ci.hole_14
                     WHEN h.hole = 15 THEN ci.hole_15
                     WHEN h.hole = 16 THEN ci.hole_16
                     WHEN h.hole = 17 THEN ci.hole_17
                     WHEN h.hole = 18 THEN ci.hole_18
                END AS par
              , CASE WHEN h.hole = 1  THEN tp.hole_1
                     WHEN h.hole = 2  THEN tp.hole_2
                     WHEN h.hole = 3  THEN tp.hole_3
                     WHEN h.hole = 4  THEN tp.hole_4
                     WHEN h.hole = 5  THEN tp.hole_5
                     WHEN h.hole = 6  THEN tp.hole_6
                     WHEN h.hole = 7  THEN tp.hole_7
                     WHEN h.hole = 8  THEN tp.hole_8
                     WHEN h.hole = 9  THEN tp.hole_9
                     WHEN h.hole = 10 THEN tp.hole_10
                     WHEN h.hole = 11 THEN tp.hole_11
                     WHEN h.hole = 12 THEN tp.hole_12
                     WHEN h.hole = 13 THEN tp.hole_13
                     WHEN h.hole = 14 THEN tp.hole_14
                     WHEN h.hole = 15 THEN tp.hole_15
                     WHEN h.hole = 16 THEN tp.hole_16
                     WHEN h.hole = 17 THEN tp.hole_17
                     WHEN h.hole = 18 THEN tp.hole_18
                END AS score
      FROM course_info AS ci
      INNER JOIN tournaments AS tp ON ci.course = tp.course
      CROSS JOIN holes18 as h
      WHERE ci.type = 'par'
              AND ci.tee = 'black'
              AND tp.player_name LIKE '%%Player, Sample%%'
              AND tp.season = '2014'
              AND tp.tournament = 'Spring Handicap'
              AND tp.course = 'Augusta National'
      ) as ts
GROUP BY
          course
        , player_name
        , season
        , tournament
;

Open in new window

Also, if you don't want the table `holes18` you just just do a small union query instead:
cross join (
            select 1  as hole union all
            select 2  as hole union all
            select 3  as hole union all
            select 4  as hole union all
            select 5  as hole union all
            select 6  as hole union all
            select 7  as hole union all
            select 8  as hole union all
            select 9  as hole union all
            select 10 as hole union all
            select 11 as hole union all
            select 12 as hole union all
            select 13 as hole union all
            select 14 as hole union all
            select 15 as hole union all
            select 16 as hole union all
            select 17 as hole union all
            select 18 as hole
           ) as h

Open in new window

* Views:
As you have your main data de-normalized I probably would use views to "unpivot"
(well I would probably normalize the tables, but assuming you don't want to do that, then views would be damn handy)
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39635662
No points for this, but as a very bad golfer I'd like to point out that birdies, pars and bogeys are only three outcomes among many that would be accounted for.  A hole-in-one on a par 3 is not uncommon in tournament play, and that is an eagle.  On a par 4, it is quite uncommon, but occasionally a player gets a 2 on a par 5, and that is a double-eagle.  Over on my end of the ledger, you'll find +2, +3, etc.  These are double bogey, triple bogey, etc.  Once you've made twice par your fellow players are usually happy for you to put the ball in your pocket until the next tee... where the humiliation can begin all over again.
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 39635734
@PortletPaul - can you explain what "normalize" the data means?  Keep in mind that the tournament data comes to me in a csv format (without any course data, just hole-by-hole scores).  I'm still somewhat new to this all.

@Ray_Paseur - Good points about the double eagle and double bogeys.  I'm still toying with how to present the data whether it is "birdies or better" and "bogeys and worse" or just birdies and bogeys.  And for what it's worth, humiliation begins on the first tee with the redemption finally coming on the 19th tee!
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 39635768
Hmmm...I see what you mean by normalization.  I thought I had done that by splitting the scoring data from the course info but the scoring data table does indeed contain redundant data.  However, I think I will have to proceed with its de-normalization due to the way the data comes to me after every tournament.  Unless you can offer some insight to how I might design the database more efficiently.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39636348
Hi, I can understand how your tables are laid out because it reflects the scorecard.

Quite simply, any set of fields that has a common prefix followed by a numeric suffix is a perfect candidate for normalization e.g. hole_  + 1,2,3,...18

With the data laid out in that fashion you will frequently find yourself needing to provide 18 sub-conditions and 17 operators in queries e.g.

CASE WHEN td.hole_1<course.hole_1 THEN 1 Else 0 End +
 CASE WHEN td.hole_2<course.hole_2 THEN 1 Else 0 End +
 CASE WHEN td.hole_3<course.hole_3 THEN 1 Else 0 End +
 CASE WHEN td.hole_4<course.hole_4 THEN 1 Else 0 End +
 CASE WHEN td.hole_5<course.hole_5 THEN 1 Else 0 End +
 CASE WHEN td.hole_6<course.hole_6 THEN 1 Else 0 End +
 CASE WHEN td.hole_7<course.hole_7 THEN 1 Else 0 End +
 CASE WHEN td.hole_8<course.hole_8 THEN 1 Else 0 End +
 CASE WHEN td.hole_9<course.hole_9 THEN 1 Else 0 End +
 CASE WHEN td.hole_10<course.hole_10 THEN 1 Else 0 End +
 CASE WHEN td.hole_11<course.hole_11 THEN 1 Else 0 End +
 CASE WHEN td.hole_12<course.hole_12 THEN 1 Else 0 End +
 CASE WHEN td.hole_13<course.hole_13 THEN 1 Else 0 End +
 CASE WHEN td.hole_14<course.hole_14 THEN 1 Else 0 End +
 CASE WHEN td.hole_15<course.hole_15 THEN 1 Else 0 End +
 CASE WHEN td.hole_16<course.hole_16 THEN 1 Else 0 End +
 CASE WHEN td.hole_17<course.hole_17 THEN 1 Else 0 End +
 CASE WHEN td.hole_18<course.hole_18 THEN 1 Else 0 End

in reality it's one logical condition:  CASE WHEN td.hole < course.hole  THEN 1 Else 0 End
but you are forced by the table structure to make it more complex.

Depending on how large those tables will be, a workable approach would be to use views to transpose those tables into a normalized structure, then you base queries such as the one wanted by this question on the views.

The most efficient approach would be to normalize the data directly into tables,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39636363
Oh, to Ray's comments, yes there are greater complexities to golf's scoring nomenclature and even more reason for normalization, imagine taking that original query and expanding it for albatross through triple bogey (7 sets of calculations, not 3).

I measure my rounds by the number of balls lost, 18 for me is not an unusual score.
Don't ever invite me to play a round.
0
 
LVL 2

Author Closing Comment

by:mcnuttlaw
ID: 39639014
Thanks for the tutorial on table views.  I'll give that a try.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question