Tuesday, December 2, 2014

get percentages on SQL querry - Oracle

If we want to get the percentage of some column on same query, we can do it using "ratio_to_report() over()" function.
ratio_to_report(Number_of_books) over()
let's say we have following table called tbl_books.

Book Number_of_books
Book1 30
Book1 20
Book1 10

if we want to get percentages of each book over total number of books we can do as following,
select book,ratio_to_report(Number_of_books) over() * 100 as percentage from tbl_books
it gives,

Book percentage
Book1 50
Book1 33.333333333
Book1 16.66666666