Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

Question

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:

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 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 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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;

• Also utilizing a shorter method of counting the doubles in the outer SELECT.
  Details in this related answer.

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.

Answer 5

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

Answer 6

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: https://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