Ask the Expert

Counts and percentages

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;

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: