SQL find the maximum value-CodePudding

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.

Any help is appreciated. Thank you!

CodePudding user response：

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

CodePudding user response：

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)