MySQL: GROUP BY within ranges

Question

I have a table with scores like this:

  score  |  user
 -------------------
     2   |  Mark
     4   |  Alex
     3   |  John
     2   |  Elliot
    10   |  Joe
     5   |  Dude

The table is gigantic in reality and the real scores goes from 1 to 25.

I need this:

  range   |  counts
 -------------------
   1-2    |  2
   3-4    |  2
   5-6    |  1
   7-8    |  0
   9-10   |  1

I've found some MySQL solutions but they seemed to be pretty complex some of them even suggested UNION but performance is very important. As mentioned, the table is huge.

So I thought why don't you simply have a query like this:

SELECT COUNT(*) as counts FROM score_table GROUP BY score

I get this:

  score  |  counts
 -------------------
    1    |   0
    2    |   2
    3    |   1
    4    |   1
    5    |   1
    6    |   0
    7    |   0
    8    |   0
    9    |   0
   10    |   1

And then with PHP, sum the count of scores of the specific ranges? Is this even worse for performance or is there a simple solution that I am missing?

Or you could probaly even make a JavaScript solution...

For small results sets like you have demo-d either solution is probably about the same, as you get into thousands of rows MySQL solutions are going to be better — Cheruvian, Jul 09 '14 at 20:15
@Cheruvian The table is huge in reality. But the scores goes only from 1 to 25. — yoshi, Jul 09 '14 at 20:18
Then I can't imagine it making much difference. I would GUESS that the MySQL version would be slightly faster but I advise you to benchmark each. — Cheruvian, Jul 09 '14 at 20:20
@Strawberry In this example yes. But in my reality it would be 10. The score is from 1 to max 25. — yoshi, Jul 09 '14 at 20:28
And do you know for certain in advance that the max value is 25 and the in value is 1 — Strawberry, Jul 09 '14 at 20:31
Yes. I know for certain that the max value is 25 and the min value is 1. — yoshi, Jul 09 '14 at 20:36
I have a solution but it only works for existing rows, so the entry ` 7-8 | 0` won't be there. Is that a requirement? — Alexandre Santos, Jul 09 '14 at 21:00

Gordon Linoff · Accepted Answer · 2014-07-09T20:43:25.060

3

Your solution:

SELECT score, COUNT(*) as counts
FROM score_table
GROUP BY score
ORDER BY score;

However, this will not returns values of 0 for count. Assuming you have examples for all scores, then the full list of scores is not an issue. You just won't get counts of zero.

You can do what you want with something like:

select (case when score between 1 and 2 then '1-2'
             when score between 3 and 4 then '3-4'
             . . .
        end) as scorerange, count(*) as count
from score_table
group by scorerange
order by min(score);

There is no reason to do additional processing in php. This type of query is quite typical for SQL.

EDIT:

According to the MySQL documentation, you can use a column alias in the group by. Here is the exact quote:

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

edited Jul 09 '14 at 20:43

answered Jul 09 '14 at 20:30

Gordon Linoff

1,242,037
58
646
786

Can you group by scorerange alias? http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by – xQbert Jul 09 '14 at 20:35
@xQbert For MySQL: [you can GROUP BY alias names](https://dev.mysql.com/doc/refman/5.6/en/problems-with-alias.html): **An alias can be used in a query select list to give a column a different name. You can use the alias in `GROUP BY, ORDER BY`, or `HAVING` clauses to refer to the column:** – VMai Jul 09 '14 at 20:42
@GordonLinoff Ok but I am not sure if it matters that it will not return any value for zero count, because in PHP I could just do something like this `Range1To2 = 0 + CountForScore1 + CountForScore2` – yoshi Jul 09 '14 at 20:44

score 1 · Answer 2 · answered Jul 09 '14 at 20:20

1

SELECT SUM( CASE WHEN score between 1 and 2 THEN ...

answered Jul 09 '14 at 20:20

user3741598

297
1
12

This solution isn't very elegant and flexible. Now you've to make a case for each score scope! – ArjanSchouten Jul 09 '14 at 20:21
And also is this really faster than a PHP solution where I just sum the count of scores of the specific ranges? – yoshi Jul 09 '14 at 20:22
@arjan so do better don't just critique. – xQbert Jul 09 '14 at 20:32
@yoshi depending on data set size most likley. The larger the size the more I would be on the RDBMS. IN most cases using a case like this would take fractions of seconds for the DB to complete on millions of rows. I would challenge most anyone to do simple data manipulation such as this faster especially as the data sets get larger. Also keep in mind you have to get the raw data back, that's more network traffic, bigger data sets etc. Cost is just not time, but it's bandwith as well. – xQbert Jul 09 '14 at 20:33
In my humble opinion yes. Review Gordon's answer as well He makes comment that this is quite typical for SQL; and I agree. I think @user3741598 response is justified though incomplete for a full answer. If this wasn't mysql I'd create a function based index and have the value set there on insert or update. So as data was entered the value was set and it would be an even simpler statement such as your original but grouping by the function based index column. – xQbert Jul 09 '14 at 20:41

score 1 · Answer 3 · answered Jul 09 '14 at 20:31

Honestly, I can't tell you if this is faster than passing "SELECT COUNT(*) as counts FROM score_table GROUP BY score" into PHP and letting PHP handle it...but it add a level of flexibility to your setup. Create a three column table as 'group_ID', 'score','range'. insert values into it to get your groupings right

1,1,1-2

1,2,1-2

1,3,3-4

1,4,3-4

etc...

Join to it on score, group by range. THe addition of the 'group_ID' allows you to set groups...maybe have group 1 break it into groups of two, and let a group_ID = 2 be a 5 set range (or whatever you might want).

I find the table use like this is decently fast, requires little code changing, and can readily be added to if you require additional groupings or if the groupings change (if you do the groupings in code, the entire case section needs to be redone to change the groupings slightly).

score 1 · Answer 4 · edited Jul 22 '14 at 07:57

If you want a simple solution which is very powerful, add an extra field within your table and put a value in it for the score so 1 and 2 have the value 1, 3 and 4 has 2. With that you can group by that value. Only by inserting the score you've to add an extra field. So your table looks like this:

score  |  user     |   range
--------------------------
   2   |  Mark     |   1
   4   |  Alex     |   2
   3   |  John     |   2
   2   |  Elliot   |   1
  10   |  Joe      |   5
   5   |  Dude     |   3

Now you can do:

select count(score),range from table group by range;

This is always faster if you've an application where selecting has prior.

By inserting do this:

$scoreRange = 2;
$range = ceil($score/$scoreRange);

Alexandre Santos · Answer 5 · 2014-07-09T21:24:40.273

How about this:

select concat((score + (1 * (score mod 2)))-1,'-',(score + (1 * (score mod 2)))) as score, count(*) from TBL1 group by (score + (1 * (score mod 2)))

You can see it working in this fiddle: http://sqlfiddle.com/#!2/215839/6

For the input

  score  |  user
 -------------------
     2   |  Mark
     4   |  Alex
     3   |  John
     2   |  Elliot
    10   |  Joe
     5   |  Dude

It generates this:

  range   |  counts
 -------------------
   1-2    |  2
   3-4    |  2
   5-6    |  1
   9-10   |  1

MySQL: GROUP BY within ranges

5 Answers5