Skip to content
Advertisement

SQL find the maximum value

I have 2 tables:

Customer table

CustomerId FirstName LastName Country
1 Luís Gonçalves Brazil
2 Leonie Köhler Germany
3 François Tremblay Canada
4 Bjørn Hansen Norway
52 Emma Jones United Kingdom
53 Phil Hughes United Kingdom

Invoice table

InvoiceId CustomerId Total
1 2 1.98
2 4 3.96
3 8 5.94
140 52 23.76
369 52 13.86
283 53 28.71
109 53 8.91

I have to write a query that returns the country along with the top customer and how much they spent. For countries where the top amount spent is shared, provide all customers who spent this amount.

I wrote a query like:

SELECT c.CustomerId, c.FirstName, c.LastName, c.Country, SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
GROUP BY c.FirstName, c.LastName
HAVING i.Total >= MAX(i.Total)
ORDER BY c.Country;

the query is not finding the maximum values but it is returning all available values. I am not sure about which DBMS is used as it is my first steps in SQL coding and above example is from Udacity learning platform lab (Maybe it is SQLite that they are using in the lab)

Any help is appreciated. Thank you!

Advertisement

Answer

Below code worked fine to fulfill all the requirements:

 WITH tab1 AS
( SELECT c.CustomerId, c.FirstName, c.LastName,c.Country, SUM(i.Total) as TotalSpent
FROM Customer AS c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY C.CustomerId
)
SELECT tab1.*
FROM tab1
JOIN
( SELECT CustomerId, FirstName, LastName, Country, MAX(TotalSpent) AS TotalSpent
FROM tab1
GROUP BY Country
)tab2
ON tab1.Country = tab2.Country
WHERE tab1.TotalSpent = tab2.TotalSpent
ORDER BY Country;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement