I am trying to show the customer number who has made the highest payment amount-CodePudding

Payment table

Select customerNumber from Payments 
where Amount = (Select Sum(amount) from products order by customerNumber)

Here, customer number is repeating. So, I have calculate the sum than the highest amount but in my query I am not getting the result.

''' CREATE TABLE payments ( customerNumber int(11) NOT NULL, checkNumber varchar(50) NOT NULL, paymentDate date NOT NULL, amount decimal(10,2) NOT NULL, PRIMARY KEY (customerNumber,checkNumber), CONSTRAINT payments_ibfk_1 FOREIGN KEY (customerNumber) REFERENCES customers (customerNumber) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table payments */

insert into payments(customerNumber,checkNumber,paymentDate,amount) values

(103,'HQ336336','2004-10-19','6066.78'),

(103,'JM555205','2003-06-05','14571.44'),

(103,'OM314933','2004-12-18','1676.14'),

(112,'BO864823','2004-12-17','14191.12'),

(112,'HQ55022','2003-06-06','32641.98'),

(112,'ND748579','2004-08-20','33347.88'),

(114,'GG31455','2003-05-20','45864.03'),

(114,'MA765515','2004-12-15','82261.22'),

(114,'NP603840','2003-05-31','7565.08'),

(114,'NR27552','2004-03-10','44894.74'),

(119,'DB933704','2004-11-14','19501.82'),

(119,'LN373447','2004-08-08','47924.19'),

(119,'NG94694','2005-02-22','49523.67'),

(121,'DB889831','2003-02-16','50218.95'),

(121,'FD317790','2003-10-28','1491.38'),

(121,'KI831359','2004-11-04','17876.32'),

(121,'MA302151','2004-11-28','34638.14'),

(124,'AE215433','2005-03-05','101244.59'),

(124,'BG255406','2004-08-28','85410.87'),

(124,'CQ287967','2003-04-11','11044.30'),

(124,'ET64396','2005-04-16','83598.04'),

(124,'HI366474','2004-12-27','47142.70'),

(124,'HR86578','2004-11-02','55639.66'),

(124,'KI131716','2003-08-15','111654.40'),

(124,'LF217299','2004-03-26','43369.30'),

(124,'NT141748','2003-11-25','45084.38'),

(128,'DI925118','2003-01-28','10549.01'),

(128,'FA465482','2003-10-18','24101.81'),

(128,'FH668230','2004-03-24','33820.62'),

(128,'IP383901','2004-11-18','7466.32'),

(129,'DM826140','2004-12-08','26248.78'),

(129,'ID449593','2003-12-11','23923.93'),

(129,'PI42991','2003-04-09','16537.85'),

(131,'CL442705','2003-03-12','22292.62'),

(131,'MA724562','2004-12-02','50025.35');

CodePudding user response：

Using a LIMIT query we can try:

SELECT customerNumber
FROM Payments
GROUP BY customerNumber
ORDER BY SUM(amount) DESC
LIMIT 1;

If you need to cater to the possibility of there being more than one customer tied for the highest total, then we can either use a subquery or the RANK analytic function. Using the former:

SELECT customerNumber
FROM Payments
GROUP BY customerNumber
HAVING SUM(amount) = (SELECT SUM(amount) FROM Payments GROUP BY customerNumber
                      ORDER BY 1 DESC LIMIT 1);