Sort Data in a Range MySQL

Question

I have a table like below

SUBJECT  - MARKS - SEMESTER
MATH     - 50    -  1
SCIENCE  - 60    -  1
ENGLISH  - 70    -  1
MATH     - 60    -  2
SCIENCE  - 80    -  2
ENGLISH  - 90    -  2

I want to produce a output like below. The problem is, even there is no data between 0-10 range I want 0 in all three columns. I am unable to achieve using "group by" and "sum". Do any of you have any idea

RANGE  MATH SCIENCE ENGLISH
0-10      0     0       0
10-20     0     0       0
20-30     0     0       0
30-40     0     0       0
40-50     0     0       0
50-60     1     0       0
60-70     1     1       0
70-80     0     0       1
80-90     0     1       0
90-100    0     0       1

possible duplicate: http://stackoverflow.com/questions/7674786/mysql-pivot-table — FirebladeDan, Aug 20 '15 at 01:17

score 1 · Accepted Answer · answered Aug 20 '15 at 01:18

You can do this, but you need to define the ranges, either as a reference table or in the query. The rest is conditional aggregation:

select r.range,
       sum(subject = 'MATH' and t.marks is not null) as Math,
       sum(subject = 'SCIENCE' and t.marks is not null) as Science,
       sum(subject = 'English' and t.marks is not null) as English
from ((select 0 as mins, 9.99 as maxs, '0-10' as range) union all
      (select 10 as mins, 19.99 as maxs, '10-20' as range) union all
      . . .
      (select 90 as mins, 100 as maxs, '90-100' as range)
     ) left join
     table t
     on t.marks between r.mins and r.maxs
group by r.range
order by min(r.mins);

Sort Data in a Range MySQL

1 Answers1