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.
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
- Dozens more answers to tough Oracle questions from Frank Kulash are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.
This was first published in August 2003