Maximum and minimum payment

Question

Maximum and minimum payment

Navigation

#1 by (2 votes)

2

I have two tables, customers and payments , I want to check for each client which was the highest and lowest payment that each one made. I did this SQL, but it did not work, could anyone help me?

SELECT c.customerName, max(p.amount), min(p.amount)
FROM customers c, payments p 
WHERE p.customerNumber = c.customerNumber 
ORDER BY c.customerNumber

sql select

asked by anonymous 20.10.2015 / 18:06

1 answer

Get URL value in a DropDownList Removal item in threaded list C

score 2 · Answer 1

The GROUP BY clause in your SELECT is missing.

SELECT c.customerName, max(p.amount), min(p.amount)
FROM customers c, payments p 
WHERE p.customerNumber = c.customerNumber 
GROUP BY c.customerNumber
ORDER BY c.customerNumber

Another way you can do is to use JOIN:

SELECT c.customerName, max(p.amount), min(p.amount)
FROM customers c
JOIN payments p ON p.customerNumber = c.customerNumber
GROUP BY c.customerNumber
ORDER BY c.customerNumber