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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top