Solved

Create a query with 3 SELECT and multiple CASE

Posted on 2013-11-08
11
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 41

Expert Comment

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

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 49

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 49

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 110

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 49

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 49

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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