Oracle SQL query: how to use count

Question

I have data in following format

userid  amount  term  APR
1        10       5    1
1        10       4    2
2        20       6    1
2        20       4    3
2        20       3    1

I want to do order by on amount, term, APR so in output I want maximum amount and it's corresponding term, APR. In case amount is same, choose one with max term and same happens if term is also same. but combination of these three is always unique.

output columns:

userid,  amount, term, apr, numberOfRowsForEachUser
  1       10       5   1      2
  2       20       6   1      3

Question: I am able to get first four columns, but not sure how to get 'total no of offers' or 'total number of rows per user'.

My query looks like this.

select 
  userid,amount,term,apr
  from 
( select userid, amount, term,  apr
   RANK() OVER (PARTITION BY userid ORDER BY amount,term,apr) amount_rank,   
from 
   tableXYZ
) 
where amount_rank = 1

Justin Cave · Accepted Answer · 2012-08-03 19:53:48Z

5

Just add a COUNT(*) analytic function

select 
  userid,amount,term,apr, cnt
  from 
( select userid, amount, term,  apr
   RANK() OVER (PARTITION BY userid ORDER BY amount,term,apr) amount_rank,   
   COUNT(*) OVER (PARTITION BY userid) cnt
from 
   tableXYZ
) 
where amount_rank = 1

answered Aug 3, 2012 at 19:53

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nir Alfasi · Accepted Answer · 2012-08-03 20:23:44Z

2

I liked Justing answer (+1) but still wanted to provide an alternative answer which is more intuitive to me:

select x.userid,x.amount,x.term,x.apr,y.cnt
from tableXYZ x, (select userid, COUNT(1) cnt from tableXYZ group by userid) y
where x.term = (select max(term) from tableXYZ xx where xx.userid = x.userid)
and x.userid = y.userid

You can play with it here

edited Aug 3, 2012 at 20:23

answered Aug 3, 2012 at 20:12

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Collectives™ on Stack Overflow

Oracle SQL query: how to use count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related