Introduction to SQL Grouping and Percentage Calculation

As a data analyst or programmer, working with large datasets can be challenging. One common task is comparing the count of groups in percentage terms. In this article, we will explore how to achieve this using SQL.

PostgreSQL provides several methods for grouping data and calculating percentages. In this post, we’ll delve into one method: using aggregate functions and conditional statements to calculate percentages.

Understanding Grouping and Aggregate Functions

In PostgreSQL, groupings are used to divide a dataset into categories or groups. The GROUP BY clause is used to specify the columns that should be grouped together. Aggregate functions such as COUNT(), SUM(), and AVG() can then be applied to calculate various statistics.

For example, let’s consider a table with student information:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    class_id INTEGER
);

We have two tables: students and enroll. The enroll table has the following schema:

CREATE TABLE enroll (
    student_id INTEGER PRIMARY KEY,
    class_id INTEGER
);

Calculating Class Enrollment

To find the number of enrolled students for each class, we can use a simple GROUP BY query:

SELECT E.class_id, COUNT(*)
FROM enroll E
GROUP BY E.class_id;

This will return a list of classes with their corresponding enrollment counts.

Calculating Class Average and Percentage Comparison

Now that we have the enrollment counts for each class, we can calculate the average count and compare it to the desired threshold (10% above the average).

To do this, we’ll use the AVG() function to calculate the average enrollment count:

SELECT E.class_id, AVG(E.ct) AS avg_enrollment
FROM (
    SELECT COUNT(*) ct, class_id
    FROM enroll
    GROUP BY class_id
) E
GROUP BY E.class_id;

In this query, we first use a subquery to calculate the enrollment counts for each class. We then use the AVG() function to calculate the average count.

Comparing Class Enrollment with Threshold

To compare the class enrollment count to the threshold (10% above the average), we can use a conditional statement:

SELECT E.class_id, E.ct,
    AVG(E.ct) AS avg_enrollment,
    CASE WHEN E.ct > (E.avg_enrollment * 1.1) THEN 'Above Threshold' ELSE 'Below Threshold' END AS status
FROM (
    SELECT COUNT(*) ct, class_id,
        AVG(COUNT(*)) OVER () AS avg_enrollment
    FROM enroll
    GROUP BY class_id
) E
GROUP BY E.class_id;

In this query, we use a subquery to calculate the average enrollment count. We then compare each class enrollment count to 10% above the average using a conditional statement. If the count is above threshold, it displays ‘Above Threshold’, otherwise it displays ‘Below Threshold’.

Using Having Clause

The previous example uses a case statement in the SELECT clause. However, another approach is to use the HAVING clause instead of the CASE statement.

SELECT E.class_id, COUNT(E.ct) AS ct,
    AVG(COUNT(*)) OVER () AS avg_enrollment,
    COUNT(DISTINCT CASE WHEN COUNT(*) > (AVG(COUNT(*)) * 1.1) THEN student_id END)
FROM enroll E
GROUP BY E.class_id
HAVING AVG(COUNT(*)) OVER () * 1.1 < COUNT(E.ct);

Calculating Percentage of Total Enrolled Students

To calculate the percentage of total enrolled students for each class, we need to first calculate the total number of enrolled students across all classes.

WITH enrollments AS (
    SELECT E.class_id, E.ct
    FROM (
        SELECT COUNT(*) ct, class_id
        FROM enroll
        GROUP BY class_id
    ) E
)
SELECT RE.class_id,
       (RE.ct / TE.total) * 100 AS percentage_enrolled,
       TE.total
FROM enrollments RE
LEFT JOIN (
    SELECT class_id, COUNT(DISTINCT student_id) AS total
    FROM enroll
    GROUP BY class_id
) TE ON RE.class_id = TE.class_id;

Conclusion

In this post, we have explored how to compare the count of groups in percentage terms using SQL. We used aggregate functions and conditional statements to calculate percentages.

We also covered various methods to achieve the same result, including using subqueries and joining tables.

Whether you are working with a large dataset or just starting out with SQL, understanding groupings and aggregate functions is essential for effective data analysis.

Last modified on 2024-03-28