I want to show the COUNT of a column and what percentage of the total that COUNT is. This does not work in Oracle...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.