OraFAQ Forum: SQL & PL/SQL » trying to get percentage of count

Home » SQL & PL/SQL » SQL & PL/SQL » trying to get percentage of count

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trying to get percentage of count [message #230711]

Thu, 12 April 2007 11:14

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!

Report message to a moderator

Re: trying to get percentage of count [message #230722 is a reply to message #230711]

Thu, 12 April 2007 11:38

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')

Report message to a moderator

Re: trying to get percentage of count [message #230806 is a reply to message #230722]

Thu, 12 April 2007 23:14

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	bitmap index
Next Topic:	creating auto generation column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 06:28:01 CST 2024