Home » SQL & PL/SQL » SQL & PL/SQL » trying to get percentage of count
trying to get percentage of count [message #230711] Thu, 12 April 2007 11:14 Go to next message
dimples0109
Messages: 6
Registered: March 2007
Junior Member
I have a table (A) where I am getting the total # of all accounts. Then I want to get the totat number of accounts listed by code. So I need output like:

Total Accounts: 275,000

Count Percentage

Totat delinquent accunts: 25,000 %
Totatl tagged accounts: 50,000 %
Total TRM accounts: 200,000 %

Can I combine these into one query? Also I cant figure out how to get the percentage of the counts.

This is what I have so far:

select count (account) from table A where code in ('delinquent', 'tagged', 'trm')
....
how do I get the percentage?

thanks!
Re: trying to get percentage of count [message #230722 is a reply to message #230711] Thu, 12 April 2007 11:38 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You need something like:

select 
(count (case when code = 'deliquent' then 1 end)/count(*))*100 as dlq_acc_pct,
(count (case when code = 'tagged' then 1 end)/count(*))*100 as tag_acc_pct,
(count (case when code = 'trm' then 1 end)/count(*))*100 as trm_acc_pct
from tableX
where code in ('delinquent', 'tagged', 'trm')
Re: trying to get percentage of count [message #230806 is a reply to message #230722] Thu, 12 April 2007 23:14 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can use RATIO_TO_REPORT() to get relative percentages, and ROLLUP to print the total line.

SQL> select nvl(object_type, 'TOTAL') AS object_type, sum(cnt), round(sum(pct),2)
  2  from (
  3    select object_type, cnt, 100 * ratio_to_report(cnt) OVER () AS pct
  4    from (
  5      select object_type, count(*) AS cnt
  6      from dba_objects
  7      where object_type in ('INDEX','TABLE','VIEW')
  8      group by object_type
  9    )
 10  )
 11  GROUP BY ROLLUP(object_type)
 12  /

OBJECT_TYPE           SUM(CNT) ROUND(SUM(PCT),2)
------------------- ---------- -----------------
INDEX                     4862              44.4
TABLE                     2733             24.96
VIEW                      3355             30.64
TOTAL                    10950               100


Ross Leishman
Previous Topic: bitmap index
Next Topic: creating auto generation column
Goto Forum:
  


Current Time: Sun Dec 11 04:15:15 CST 2016

Total time taken to generate the page: 0.24223 seconds