CODEWITHSUNDEEP
sqloracle-databasedatabase
user1803009
user1803009

Reputation: 35

sql statement finding total of an amount

I am trying to write an SQL statement that can find a company's top client. A top client is defined as one that has purchased the most (total purchase amount) among all the company's clients, not just a the max amount in 1 purchase. How would I go at solving this one? I don't have much experience with SQL but what I do have so far is: 

SELECT CLIENT.CLIENTNO, CLIENT.CLIENT NAME, PURCHASE.AMOUNT
FROM PURCHASE, CLIENT
WHERE PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME, PURCHASE.AMOUNT;

This only displays the results of all candidates. How would I start since i'm not looking for the max value of an amount but the total sum of the highest paying client?

Just going to put this as an additional question if someone could answer.. How would I change a constraint on one of my tables to be limited to a set of strings? Right now it is varchar2(25) but I want it to be only valid for like "string" "string2" help is appreciated.

Upvotes: 2

Views: 478

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16915

Try:

select CLIENTNO, CNAME
from (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, rank() over (order by sum(PURCHASE.AMOUNT) desc) rnk
FROM PURCHASE join CLIENT on PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME)
where rnk = 1;

UPDATE If you want the sum of purchase amount

select CLIENTNO, CNAME, spa PURCHASE_AMOUNT
from (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, sum(PURCHASE.AMOUNT) spa,  rank() over (order by sum(PURCHASE.AMOUNT) desc) rnk
FROM PURCHASE join CLIENT on PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME)
where rnk = 1;

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146541

Try this

 Select c.ClientNo, c.CName Name, Sum(p.Amount)
 From Client c Join Purchase p 
    On p.clientNo = c.clientNo
 Group By c.CLientNo, c.ClientName
 Having Sum(p.Amount) =
       (Select Max(SumAmt)
        From (Select Sum(Amount) SumAmt
              From Purchase 
              Group By clientNo) z)

This sql says

"Select the clientNo, Name and sum of purchase amounts for the specific client whose sum of purchase amounts is the greatest." 

 Select c.ClientNo, c.CName Name, Sum(p.Amount) -- Select the clientNo, Name and sum 
 From Client c Join Purchase p 
    On p.clientNo = c.clientNo
 Group By c.CLientNo, c.ClientName              -- for the specific client whose 
 Having Sum(p.Amount) =                         -- sum of purchase amounts is 
       (Select Max(SumAmt)                      -- Largest 
        From (Select Sum(Amount) SumAmt         -- Sum of purchase amounts
              From Purchase 
              Group By clientNo) z)             -- for each client

Upvotes: 1

Related Questions