# 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:

``````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.

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

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