Q

Counts and percentages

This Content Component encountered an error

I want to show the COUNT of a column and what percentage of the total that COUNT is. This does not work in Oracle 8i:

SELECT   nationality, 
         (
             (COUNT (*) * 100)
             /
             (
                 SELECT  COUNT (*)
                 FROM    member
             )
         )  AS percentage
FROM     member 
GROUP BY nationality 
ORDER BY nationality;

Unfortunately, you can't use a subquery (like "(SELECT COUNT (*) FROM member)") in the SELECT clause. If you had the total count of all nationalities stored in a table by itself, you could do this:

SELECT    m.nationality,
          (COUNT (m.nationality) * 100) / t.total_cnt
                  AS percentage 
FROM      member      m,
          total_table t
GROUP BY  m.nationality,
          t.total_cnt
ORDER BY  m.nationality;
This is not very satisfactory because
  • You have to take steps to guarantee that total_table hasn't grown out ot date before you run the query.
  • It wastes storage space.
  • It clutters up the data dictionary.
You can solve the first two problems by making total_table a view instead of a table. Starting with version 8.1 of Oracle, you can solve the last problem by making it an inline view.
SELECT    m.nationality,
          (COUNT (m.nationality) * 100) / t.total_cnt
                  AS percentage 
FROM      member      m,
          (
              SELECT  COUNT (*)    AS total_cnt
              FROM    member
          )  t
GROUP BY  m.nationality,
          t.total_cnt
ORDER BY  m.nationality;
Whenever you find yourself thinking "I wish there was a table that ..." or "It would be a lot simpler if there was a table that ...", then think of views, particularly inline views. This happens a lot when you have to deal with group functions. For example, I look at the query above and think "It would be a lot simpler if there was a table that had one line per nationality." So I'd write it with a second inline view, the result set of which was a table with one line per nationality:
SELECT   n.nationality,
         (n.nationality_cnt * 100) / t.total_cnt
             AS percentage 
FROM     (
              SELECT    nationality,
                        COUNT (*)     AS nationality_cnt
              FROM      member
              GROUP BY  nationality
         )  n,
         (
             SELECT  COUNT (*) AS total_cnt
             FROM    member
         )  t
ORDER BY n.nationality;

For More Information


This was first published in August 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close