CODEWITHSUNDEEP
mysqlsql
rafaelrezend
rafaelrezend

Reputation: 1015

Conditional cumulative SUM in MySQL

I have the following table:

+-----+-----------+----------+------------+------+
| key | idStudent | idCourse | hourCourse | mark |
+-----+-----------+----------+------------+------+
|   0 |         1 |        1 |         10 |   78 |
|   1 |         1 |        2 |         20 |   60 |
|   2 |         1 |        4 |         10 |   45 |
|   3 |         3 |        1 |         10 |   90 |
|   4 |         3 |        2 |         20 |   70 |
+-----+-----------+----------+------------+------+

Using a simple query, I can show student with their weighted average according to hourCourse and mark:

SELECT idStudent,
       SUM( hourCourse * mark ) / SUM( hourCourse ) AS WeightedAvg
  FROM `test`.`test`
  GROUP BY idStudent;
+-----------+-------------+
| idStudent | WeightedAvg |
+-----------+-------------+
|         1 |     60.7500 |
|         3 |     76.6667 |
+-----------+-------------+

But now I need to select the registers until the cumulative sum of hourCourse per student reaches a threshold. For example, for a threshold of 30 hourCourse, only the following registers should be taken into account:

+-----+-----------+----------+------------+------+
| key | idStudent | idCourse | hourCourse | mark |
+-----+-----------+----------+------------+------+
|   0 |         1 |        1 |         10 |   78 |
|   1 |         1 |        2 |         20 |   60 |
|   3 |         3 |        1 |         10 |   90 |
|   4 |         3 |        2 |         20 |   70 |
+-----+-----------+----------+------------+------+

key 2 is not taken into account, because idStudent 1 already reached 30 hourCourse with idCourse 1 and 2.

Finally, the query solution should be the following:

+-----------+-------------+
| idStudent | WeightedAvg |
+-----------+-------------+
|         1 |     66.0000 |
|         3 |     76.6667 |
+-----------+-------------+

Is there any way to create an inline query for this? Thanks in advance.

Edit: The criteria while selecting the courses is from highest to the lowest mark. Edit: Registers are included while the cumulative sum of hourCourse is less than 30. For instance, two registers of 20 hours each would be included (sum 40), and the following not.

Upvotes: 4

Views: 2235

Answers (1)

mellamokb
mellamokb

Reputation: 56769

You can calculate the cumulative sums per idStudent in a sub-query, then only select the results where the cumulative sum is <= 30:

select idStudent,
       SUM( hourCourse * mark ) / SUM( hourCourse ) AS WeightedAvg
from
(
  SELECT t.*,
  case when @idStudent<>t.idStudent
    then @cumSum:=hourCourse
    else @cumSum:=@cumSum+hourCourse
  end as cumSum,
  @idStudent:=t.idStudent
  FROM `test` t,
  (select @idStudent:=0,@cumSum:=0) r
  order by idStudent, `key`
) t
where t.cumSum <= 30
group by idStudent;

Demo: http://www.sqlfiddle.com/#!2/f5d07/23

Upvotes: 5

Related Questions