SQL query to find top 2 customers done max amount of transaction( oracle db)

Question

I want to get TOP 2 customers done maximum amount transaction from below the table.I am working on oracle db.

my table transactions:

 tranID       PROD_ID       QTY      PRICE        CID 
      1       100            5         10000      1000  
      2       103            16        5000       1001  
      3       102            8         5000       1003
      4       200            10        9000       1002  
      5       204            8        9000         1002  
      6       207            4         8000        1002  

CUSTOMERS

CID CNAME
1001  X
1002  Y
1003  Z

Answer 1

If Oracle 12c , you can use this.

SELECT a.CID, b.CNAME, SUM (a.QTY * a.PRICE) amount
       FROM transactions a JOIN CUSTOMERS b ON a.CID = b.CID
   GROUP BY a.CID, b.CNAME
   ORDER BY amount DESC
FETCH FIRST 2 ROWS ONLY

Answer 2

You could use a summ group by and a join and last filter for rownum

  select a.CID, b.CNAME, sum(a.QTY*a.PRICE) amount
  from transactions a
  INNER JOIN CUSTOMERS b on a.CID = b.CID
  WHERE ROWNUM < 3
  GROUP BY a.CID, b.CNAME
  ORDER BY amount 

Answer 3

This can be done using the following query. First, you compute sums, then you order it and then you select the first two rows.

SELECT t.*
FROM    
(
  SELECT t.CID, c.name, sum(t.PRICE * t.qty) tsum
  FROM transaction t
  JOIN customer c ON t.CID = c.CID
  GROUP BY t.CID , c.name
  ORDER BY tsum DESC
) t
WHERE rownum < 3