|
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) +
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) +
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as TotalIncPoints,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)
It's possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it's possible to combine
all of the example cuts of the data into one SQL statement, which provides additional options for displaying
data on your page
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2 as AVG1_2,
(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,
(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)
Exam scores are listing along with moving averages...again it's all with one
select statement.
|