0

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
sri
  • 527
  • 2
  • 6
  • 11

3 Answers3

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
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
0

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
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

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
Radim Bača
  • 10,646
  • 1
  • 19
  • 33