dimanche 19 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire