SQL find the maximum value

user_18722130 0
Viewed 66

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!

sql
3 Answers

You did not shared your database with us. Also, you need to add expected results in your question from the data you provided. But lets say you use SQLite then this would work I think:

select CustomerId
       , FirstName
       , LastName
       , Country
       , max(tot)
from ( select sum(i1.Total) as tot
              , i1.CustomerId
              , c1.Country
              , c1.FirstName
              , c1.LastName
       FROM Customer c1
       JOIN Invoice i1
       ON c1.CustomerId = i1.CustomerId
       group by i1.CustomerId) TAB
group by Country

DEMO

After the comment from the OP I have edited the code:

select c.CustomerId, c.FirstName, c.LastName, c.Country, sum(Total)
from Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
group by country, c.CustomerId
having sum(Total) in (select  max(tot) as tot2
                      from (select sum(i1.Total) as tot
                                   , country
                            FROM Customer c1
                            JOIN Invoice i1
                            ON c1.CustomerId = i1.CustomerId
                            group by i1.CustomerId) TAB
                      group by country)

DEMO2

user_65650380

As the accepted (at the time of writing this answer) solution would fail on at least PostgreSQL (for not including a selected value in either the group by clause or an aggregate function) I provide another variant:

WITH t AS
(
    SELECT
        c.customerid AS i, c.last_name  AS l, c.first_name AS f,
        c.country    AS c, SUM(i.total) AS s
    FROM customer c JOIN invoice i ON c.customerid = i.customerid 
    GROUP BY c.customerid, c.country
    -- note: last and first name do not need to be included in the group by clause
    --       here as the (assumed!) primary key 'customerid' already is
)
SELECT c, l, f, s FROM t
WHERE s = (SELECT MAX(s) FROM t WHERE t.c = c)

(tested on PostgreSQL)

user_13123820

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_187221300
Related