Maximum and minimum payment

Asked

Viewed 67 times

2

I have two tables, customers and payments, I want to verify from each customer which was the largest and smallest payment each made. I did this SQL, but it didn’t work, someone could 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

  • Just for the record, the name of the tables are in Portuguese or English?

  • Which error returned? This is the GROUP BY clause.

1 answer

2

GROUP BY clause missing in your SELECT. 

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 by using 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

Browser other questions tagged sql select

You are not signed in. Login or sign up in order to post.