Q:

Wrong data output in SQL request

I have a table named payments 
CREATE TABLE payments (
`id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
`student_id` INT NOT NULL,
`datetime` DATETIME NOT NULL,
`amount` FLOAT DEFAULT 0,
INDEX `student_id` (`student_id`)
);

It is necessary to create a query that is find all student_id whose sum payment is less than the biggest one. (it can be more than one user with the same biggest amount of payments)
Let assume for instance this is a test data:
== Dumping data for table payments
id-student_id-datetime-amount
|1|4|2015-06-11 00:00:00|2
|2|5|2015-06-01 00:00:00|6
|3|1|2015-06-03 00:00:00|8
|4|2|2015-06-02 00:00:00|9
|5|4|2015-06-09 00:00:00|6
|6|5|2015-06-06 00:00:00|3
|7|2|2015-06-05 00:00:00|6
|8|3|2015-06-09 00:00:00|12
|14|1|2015-06-01 00:00:00|0
|15|1|2015-06-03 00:00:00|7
|16|6|2015-06-02 00:00:00|0
|17|6|2015-06-07 00:00:00|0
|18|6|2015-06-05 00:00:00|0

Next query shows all students with their sum payments
SELECT `student_id`, SUM(amount) as `sumamount`
FROM `payments`  
GROUP BY `student_id`
ORDER BY `sumamount` DESC

Here is write output of this query ordered by sumamount
student_id  sumamount
1   15
2   15
3   12
5   9
4   8
6   0

BUT the problem is when I try to get the user who paid less than the biggest one it gives me the wrong answer 
Here is the query to get the second user:
SELECT `student_id`, SUM(amount) as `sumamount`
FROM `payments`  
GROUP BY `student_id`
HAVING `sumamount` < MAX(sumamount)
ORDER BY `sumamount` DESC

Here is the result
student_id  sumamount
3   12
4   8
6   0

As we can see student_id = 5 missed and I have no idea why.

A:

You need to calcualate MAX(sumamount) in a subquery, so that MAX is not grouped by student_id.
SELECT `student_id`, SUM(amount) as `sumamount`, maxsum
FROM `payments`
CROSS JOIN (SELECT MAX(sumamount) AS maxsum
            FROM (SELECT SUM(amount) AS sumamount
                  FROM payments
                  GROUP BY student_id) t1) t2
GROUP BY `student_id`
HAVING `sumamount` < maxsum
ORDER BY `sumamount` DESC

DEMO

