John Vn John Vn - 5 months ago 10
SQL Question

SQL query max value of a summarize

I am currently learning SQL and I am required to execute some SQL queries. There is one particular query that I fail to implement.


Which are the clients who have (totally) the most expensive orders?


customerid
and
total value
of all orders of the same customer must be returned as result.

The table is:

Salesorderheader
:

salesorderid (int) customerid (int) totaldue (double)
1 32000 3.20000


The database system I use is Postgresql. The query I got so far is:

SELECT totaldue, customerid
FROM salesorderheader
WHERE totaldue = (SELECT max(totaldue) FROM salesorderheader);


This query is wrong because a new value labeled as
total_value
(total value of all orders of the same customer) or something similar must be returned instead.

I know there that the SQL function
sum()
must be used combined with
GROUP BY customerid)
, but so far I failed to implement the correct query.

Thank you for your time.

NOTE: if I broke any site rules or this post is duplicate let me now and I'll delete this post immediately

Sample for table

enter image description here

Answer

this gives biggest expences by clients:

SELECT distinct max(totaldue) over (partition by customerid),customerid 
FROM salesorderheader
order by 1 desc 
;

this shows the most wasting client between all:

select sum(totaldue) over (partition by customerid),customerid 
    FROM salesorderheader
    order by 1 desc 
limit 1
    ;
Comments