oracle - sql statement finding total of an amount -
i trying write sql statement can find company's top client. top client defined 1 has purchased (total purchase amount) among company's clients, not max amount in 1 purchase. how go @ solving one? don't have experience sql have far is:
select client.clientno, client.client name, purchase.amount purchase, client purchase.clientno = client.clientno group client.clientno, client.cname, purchase.amount;
this displays results of candidates. how start since i'm not looking max value of amount total sum of highest paying client?
just going put additional question if answer.. how change constraint on 1 of tables limited set of strings? right varchar2(25) want valid "string" "string2" appreciated.
try:
select clientno, cname (select client.clientno, client.cname, rank() on (order sum(purchase.amount) desc) rnk purchase join client on purchase.clientno = client.clientno group client.clientno, client.cname) rnk = 1;
update if want sum of purchase amount
select clientno, cname, spa purchase_amount (select client.clientno, client.cname, sum(purchase.amount) spa, rank() on (order sum(purchase.amount) desc) rnk purchase join client on purchase.clientno = client.clientno group client.clientno, client.cname) rnk = 1;
Comments
Post a Comment