Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a problem: counting the number of double doubles and/or triple doubles a player made over the season from his game statistics.

The definition of a double double and a triple double is as follows:

Double-double:

A double-double is defined as a performance in which a player accumulates a double-digit number total in two of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Triple-double:

A triple-double is defined as a performance in which a player accumulates a double digit number total in three of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Quadruple-double (added for clarification)

A quadruple-double is defined as a performance in which a player accumulates a double digit number total in four of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

The "PlayerGameStats" table stores statistics for each game a player plays and looks as follows:


| ID | PLAYER_ID | SEASONDAY |    TEAM |     OPPONENT | POINTS | REBOUNDS | ASSISTS | STEALS | BLOCKS |
|----|-----------|-----------|---------|--------------|--------|----------|---------|--------|--------|
|  1 |         1 |         1 | Nuggets |    Cavaliers |      6 |        8 |       2 |      2 |      0 |
|  2 |         1 |         2 | Nuggets |     Clippers |     15 |        7 |       0 |      1 |      3 |
|  3 |         1 |         6 | Nuggets | Trailblazers |     11 |       11 |       1 |      2 |      1 |
|  4 |         1 |        10 | Nuggets |    Mavericks |      8 |       10 |       2 |      2 |     12 |
|  5 |         1 |        11 | Nuggets |       Knicks |     23 |       12 |       1 |      0 |      0 |
|  6 |         1 |        12 | Nuggets |         Jazz |      8 |        8 |      11 |      1 |      0 |
|  7 |         1 |        13 | Nuggets |         Suns |      7 |       11 |       2 |      2 |      1 |
|  8 |         1 |        14 | Nuggets |        Kings |     10 |       15 |       0 |      3 |      1 |
|  9 |         1 |        15 | Nuggets |        Kings |      9 |        7 |       5 |      0 |      4 |
| 10 |         1 |        17 | Nuggets |      Thunder |     13 |       10 |      10 |      1 |      0 |

The output I want to achieve looks like this:

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

The only solution I found so far is so awful it makes me puke ... ;o) ... It looks like this:

SELECT 
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10) 
                THEN 1 
                ELSE 0 
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

... and now you're probably also puking (or laughing hard) after reading this. I didn't even write out everything that would be needed to get all double double combinations, and omitted the case statement for the triple doubles because it's even more ridiculous.

Is there a better way to do this? Either with the table structure I have or with a new table structure (I could write a script to convert the table).

I can use MySQL 5.5 or PostgreSQL 9.2.

Here is a link to SqlFiddle with example data and my awful solution I posted above: http://sqlfiddle.com/#!2/af6101/3

Note that I'm not really interested in quadruple-doubles (see above) since they don't occur in the game I play as far as I know, but it would be a plus if the query is easily expandable without much rewrite to account for quadruple-doubles.

share|improve this question
7  
Best written, clear, detailed question ever. Want to help but one hand hunt and peck typing while trying to keep the baby asleep... –  Craig Ringer Jun 4 at 14:53
1  
A quick thought of an approach which might work: pivot the stats columns into rows, ie two new columns, the first being "statistic" with values "points", "rebounds", "assists", etc and the second being "value", then select where "value" >= 10, group by the player and select having count(*) >= 2 for doubleDoubles or count(*) >= 3 for tripleDoubles. –  Colin 't Hart Jun 4 at 14:58
    
@CraigRinger - thanks for the kind words, and yes, the baby has priority of course. :) –  keth Jun 4 at 15:07
2  
Can you clarify this: If I get 10 points, 10 rebounds, 10 assists, 10 steals, that would make FOUR doubles. Should this count as a Triple Double for me? (Kind of like scoring four goals in hockey is still a hat trick, isn't it?) –  Joshua Huber Jun 4 at 15:57
1  
There is no MySQL 5.4 version. Please edit with your version (5.0, 5.1, 5.5, 5.6, 5.7) –  ypercube Jun 4 at 16:42

6 Answers 6

up vote 10 down vote accepted

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work

select a.player_id, 
a.team, 
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, 
sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles
from
(select *, 
(case when points > 9 then 1 else 0 end) +
(case when rebounds > 9 then 1 else 0 end) +
(case when assists > 9 then 1 else 0 end) +
(case when steals > 9 then 1 else 0 end) +
(case when blocks > 9 then 1 else 0  end) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team
share|improve this answer
    
Hi, thank you for you're solution. I really like it. Does exactly what I want and is easily extendable to include Quadruple-double and Quintuple-doubles without much writing. Will make this the accepted answer for now. :) –  keth Jun 4 at 15:56
    
I like your code, but you can hack it to be even shorter. No need to use CASE statements since boolean expressions evaluate to 1 when true and 0 when false. I've added it to my answer below with shout out to you since can't post full SQL code block in comment here. –  Joshua Huber Jun 4 at 16:05
    
Thanks Joshua. Totally overlooked that and it looks much better. –  JChao Jun 4 at 16:13
1  
@JoshuaHuber Right but then the query will only work in MySQL. Using CASE and SUM/COUNT allows it to work on Postgres as well. –  ypercube Jun 4 at 16:15
    
@ypercube: Actually, adding up booleans works in Postgres, too. You only need to cast explicitly. But CASE is typically a tiny bit faster. I added a demo with a few other minor improvements. –  Erwin Brandstetter Jun 5 at 8:38

Try this out (worked for me on MySQL 5.5):

SELECT 
  player_id,
  team,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 2
  ) double_doubles,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 3
  ) triple_doubles
FROM PlayerGameStats
GROUP BY player_id, team

Or even shorter, by blatanly ripping off JChao's code from his answer, but taking out the unneeded CASE statements since boolean expr evaluates to {1,0} when {True,False}:

select a.player_id, 
a.team, 
sum(a.doubles = 2) as doubleDoubles, 
sum(a.doubles = 3) as tripleDoubles
from
(select *, 
(points > 9) +
(rebounds > 9) +
(assists > 9) +
(steals > 9) +
(blocks > 9) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team

Based on the comments that the above code won't run in PostgreSQL since doesn't like to do boolean + boolean. I still don't like CASE. Here's a way out on PostgreSQL (9.3), by casting to int:

select a.player_id, 
a.team, 
sum((a.doubles = 2)::int) as doubleDoubles, 
sum((a.doubles = 3)::int) as tripleDoubles
from
(select *, 
(points > 9)::int +
(rebounds > 9)::int +
(assists > 9)::int +
(steals > 9)::int +
(blocks > 9)::int as Doubles
from PlayerGameStats) a
group by a.player_id, a.team
share|improve this answer
    
@ypercube, good point & thanks. Had just asked that exact clarification as comment on the question above. Semantics. I believe four goals in hockey is still considered "pulling a hat trick", but four consecutive strikes in bowling might not be considered a "turkey" proper, rather it's a "quad". I'm no expert on each game's semantics. You make the decision and choose = or >= as fit. –  Joshua Huber Jun 4 at 16:01
    
Thanks for your solution. Definetly does what I want. Also like the shortend version from JChao you provided. –  keth Jun 4 at 16:11
1  
Adding booleans won't work in PostgreSQL though, keep that in mind. –  Craig Ringer Jun 4 at 16:17
    
@CraigRinger - thanks for pointing that out. Since I'm still green behind the ears when it comes to SQL in general and PostgreSQl in particular, this is rellay valuable information for me. :) –  keth Jun 4 at 16:26
1  
@CraigRinger Nice, but I don't think MySQL supports CAST(... AS int) (stackoverflow.com/questions/12126991/…). MySQL can do CAST(... AS UNSIGNED), which works in this query, but PostgreSQL can't. Not sure there is a common CAST that both can do for portability. Worst CASE, might be stuck with CASE in the end if portability is paramount. –  Joshua Huber Jun 4 at 16:47

Here's another take on the problem.

The way I think of it, you're essentially working with pivoted data for the current problem, so the first thing to do is unpivot it. Unfortunately PostgreSQL doesn't provide nice tools to do that, so without getting into dynamic SQL generation in PL/PgSQL, we can at least do:

SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats

This puts the data in a more malleable form, though it's sure not pretty. Here I assume that (player_id, seasonday) is sufficient to uniquely identify players, i.e. the player ID is unique across teams. If it isn't, you'll need to include enough other info to provide a unique key.

With that unpivoted data it's now possible to filter and aggregate it in useful ways, like:

SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

This is far from pretty, and it's probably not that fast. It's maintainable though, requiring minimal changes to handle new types of stats, new columns, etc.

So it's more of a "hey, did you think of" than a serious suggestion. The goal was to model the SQL to correspond to the problem statement as directly as possible, rather than to make it fast.


This was made vastly easier by your use of sane multi-valued inserts and ANSI quoting in your MySQL-oriented SQL. Thankyou; it's nice not to see backticks for once. All I had to change was the synthetic key generation.

share|improve this answer
    
This is sort of what I had in mind. –  Colin 't Hart Jun 4 at 16:23
1  
Thanks for postings this solution. Had my problems implementing something like this as @Colin'tHart suggested above (never did something like that before, but seems relly useful for some other stats I might want to caluclate in the future). It's interesting how many ways there are to accomplish my desired output. Definetly learned a lot today. –  keth Jun 4 at 16:47
1  
To learn more, explain analyze the query plans (or MySQL equivalent) and figure out what they all do and how :) –  Craig Ringer Jun 4 at 17:04
    
@CraigRinger - Thanks. Good advice. Actually kind of did that with all solutions provided until now (I used SqlFiddles "view execution plan"). But I definetly need to work on the "figure out what they all do and how" part when reading the output. =O –  keth Jun 4 at 17:29

What @Joshua displays for MySQL, works in Postgres as well. Boolean values can be cast to integer and added up. The cast needs to be explicit, though. Makes for very short code:

SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          (points   > 9)::int +
          (rebounds > 9)::int +
          (assists  > 9)::int +
          (steals   > 9)::int +
          (blocks   > 9)::int AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

However, CASE - even though more verbose - is typically a tiny bit faster. And more portable, if that should matter:

SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          CASE WHEN points   > 9 THEN 1 ELSE 0 END +
          CASE WHEN rebounds > 9 THEN 1 ELSE 0 END +
          CASE WHEN assists  > 9 THEN 1 ELSE 0 END +
          CASE WHEN steals   > 9 THEN 1 ELSE 0 END +
          CASE WHEN blocks   > 9 THEN 1 ELSE 0 END AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

SQL Fiddle.

share|improve this answer
1  
TIL: PostgreSQL, like MySQL, permits referencing columns in GROUP BY by aliases or ordinal numbers. –  Andriy M Jun 5 at 9:58

Using integer division and binary cast

SELECT player_id
     , team
     , SUM(CASE WHEN Doubles = 2 THEN 1 ELSE 0 END) DoubleDouble
     , SUM(CASE WHEN Doubles = 3 THEN 1 ELSE 0 END) TripleDouble
FROM   (SELECT player_id
             , team
             , (BINARY (points DIV 10) > 0)
             + (BINARY (rebounds DIV 10) > 0)
             + (BINARY (assists DIV 10) > 0)
             + (BINARY (steals DIV 10) > 0)
             + (BINARY (blocks DIV 10) > 0)
             AS Doubles
        FROM   PlayerGameStats) d
GROUP BY player_id, team
share|improve this answer

Just want to leave a variation of @Craig Ringers version here I found by accident, maybe it is useful for someone in the future.

Instead of multiple UNION ALL's it uses unnest and array. Source for inspiration: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql


SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT 
          player_id, 
          seasonday,
          unnest(array['Points', 'Rebounds', 'Assists', 'Steals', 'Blocks']) AS scoretype,
          unnest(array[Points, Rebounds, Assists, Steals, Blocks]) AS score
        FROM PlayerGameStats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

SQL Fiddle: http://sqlfiddle.com/#!12/4980b/3

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.