Q

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;

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 last published in August 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close