Wrong data output in SQL request

Question

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.

When I try it, I get an empty result, not the result you posted.:sqlfiddle.com/#!9/888c9/1. This is because MAX(sumamount) is controlled by GROUP BY student_id, so it gets each student's max sum. And their sum is always the same as the max, so < is never true. — Barmar
– Barmar, Commented Jun 11, 2015 at 18:52

Barmar · Accepted Answer · 2015-06-11 18:56:14Z

1

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

answered Jun 11, 2015 at 18:56

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Wrong data output in SQL request

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related