Group aggregates as fractions of overall aggregate

Group aggregates as fractions of overall aggregate

I'm working on a Web-based poll where, among other things, you fill in your gender (m,f). I would like to construct an SQL query that calculates the fractions of males and females in my table. The following returns the absolute number of people of each gender but having it in fractions is something I don't know how to do.

SELECT gender, Count(*) AS Number
FROM mytable
GROUP BY gender;

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

The fractions that you seek can only be calculated if you know the total number of people in the table. The following query will produce the correct results:

select gender
     , count(*)  as number
     , count(*) 
        / ( select count(*) 
              from mytable ) as fraction
  from mytable
group
    by gender

The subquery that acts as the divisor gets the overall count for the entire table. Furthermore, it is not correlated. This means that the database engine can run it separately ahead of time, before tackling the main query. Then, no matter how many groups are in the main query, the number obtained by the subquery can be used in the calculation of each fraction.

Note that in this type of construction, the subquery to get the overall count is a scalar subquery. Used in the SELECT list, a subquery is permitted to return only one value.

For More Information


This was first published in February 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