Solved

Create a query with 3 SELECT and multiple CASE

Posted on 2013-11-08
11
210 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
Comment Utility
Can you provide some sample data to start?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the tutorial on table views.  I'll give that a try.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now