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

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.

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.

-
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 '14 at 14:53
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 '14 at 14:58
@CraigRinger - thanks for the kind words, and yes, the baby has priority of course. :) – keth Jun 4 '14 at 15:07
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 '14 at 15:57
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 '14 at 16:42

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
``````
-
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 '14 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 '14 at 16:05
Thanks Joshua. Totally overlooked that and it looks much better. – SQLChao Jun 4 '14 at 16:13
@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 '14 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 '14 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
``````
-
@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 '14 at 16:01
Thanks for your solution. Definetly does what I want. Also like the shortend version from JChao you provided. – keth Jun 4 '14 at 16:11
Adding booleans won't work in PostgreSQL though, keep that in mind. – Craig Ringer Jun 4 '14 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 '14 at 16:26
@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 '14 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.

-
This is sort of what I had in mind. – Colin 't Hart Jun 4 '14 at 16:23
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 '14 at 16:47
To learn more, `explain analyze` the query plans (or MySQL equivalent) and figure out what they all do and how :) – Craig Ringer Jun 4 '14 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 '14 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.

-
TIL: PostgreSQL, like MySQL, permits referencing columns in GROUP BY by aliases or ordinal numbers. – Andriy M Jun 5 '14 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
``````
-

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

-