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

Questions

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

January 31, 2022

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′);

>Solution :

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);