CODEWITHSUNDEEP
sqlpostgresql
John Vn
John Vn

Reputation: 91

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

Upvotes: 0

Views: 251

Answers (2)

Eric
Eric

Reputation: 703

select top 1 customer_id, sum(total_due) as total

into #top

from salesorderheader

group by customer_id

order by sum(total_due) desc;

select * from #top t

inner join salesorderheader soh

on soh.customer_id = t.customerid;

drop table #top;

I apologize for the formatting, I'm on mobile

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51519

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
    ;

Upvotes: 1

Related Questions