TABLE 1     
STUDENT TIME    SCORE
1   1   4
1   2   3
1   3   4
2   1   2
2   2   2
2   3   8
3   3   10
3   4   10
4   1   1
4   2   3
4   3   2
4   4   4
4   5   4
4   6   5

I have TABLE 1. I wish to group and SUM(SCORE) for each STUDENT and TIME 1-2, 3-4, 5-6 to create this TABLE 2

STUDENT TIME    TOTALSCORE
1   1-2 7
1   3-4 4
1   5-6 NA
2   1-2 4
2   3-4 8
2   5-6 NA
3   1-2 NA
3   3-4 20
3   5-6 NA
4   1-2 3
4   3-4 6
4   5-6 4

However I have BIG DATA so Wish to start by doing this

select DISTINCT(TIME) from TABLE1
1
2
3
4
5
6

and then basically take all TIME values >= 1 & < 2 as T1; >=2 & < 3 as T2; it matters because we use #.# where the first # is the year and the second # is the trimester. So there are values such as 1.1, 1.2, 1.3 but I don't wish to list that out all the time

CodePudding user response：

Using integer math we can use (time-1)/2 to give us groups of all times between 1-2, 3-4, 5-6, 7-8 etc.

select   student
        ,sum(score) as total_score
        ,concat((time 1)/2*2-1, '-', (time 1)/2*2) as semester
from     t
group by student, (time 1)/2
order by student

Fiddle