Oracle SQL query: how to use an account

Question

I have data in the 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 place an order by quantity, term, APR, so at the output I want the maximum amount and the corresponding term, APR. In case the amount is the same, choose the one with the maximum term, and this will happen if the term is also the same. but the combination of these three is always unique.

column output:

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

Question: I can get the first four columns, but I'm not sure how to get the "total number of sentences" or "total number of rows per user".

My request is as follows.

 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 

Answer 1

Just add the analytic function COUNT(*)

 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 

Answer 2

I liked the Justing answer (+1), but I still wanted to provide an alternative answer that 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 him here