Multiple Select Count() in One Select from Related Tables

When working with multiple related tables, it’s common to need to perform complex queries that join these tables together. In this article, we’ll explore a specific problem where you want to display the count of students and lessons for each teacher in a single select statement.

Background

Let’s first look at the schema of our three related tables: teachers, students, and lessons.

-- teachers table
CREATE TABLE teachers (
    tch_id INT PRIMARY KEY,
    tch_name VARCHAR(255)
);

-- students table
CREATE TABLE students (
    std_id INT PRIMARY KEY,
    std_name VARCHAR(255),
    std_tch_id INT,
    FOREIGN KEY (std_tch_id) REFERENCES teachers(tch_id)
);

-- lessons table
CREATE TABLE lessons (
    les_id INT PRIMARY KEY,
    les_std_id INT,
    les_tch_id INT,
    FOREIGN KEY (les_tch_id) REFERENCES teachers(tch_id),
    FOREIGN KEY (les_std_id) REFERENCES students(std_id)
);

In this schema, a teacher can have multiple students, and each student is associated with one teacher. Similarly, a lesson is associated with one teacher and one student.

The Original Query

The original query provided in the question uses an inline subquery to compute the count of students and lessons for each teacher:

SELECT 
    tch_name,
    (SELECT COUNT(std_id) FROM students WHERE std_tch_id = tch_id) AS TotalStd,
    (SELECT COUNT(les_id) FROM lessons WHERE les_tch_id = tch_id) AS TotalLes
FROM teachers 
ORDER BY tch_id;

While this query works, it has some limitations. Let’s explore an alternative approach that can provide better performance and readability.

Using Aggregated JOINed Query

One solution to compute the counts without using inline subqueries is to use an aggregated JOINed query with COUNT(DISTINCT):

SELECT 
    t.tch_name, 
    COUNT(DISTINCT s.std_id) AS TotalStd,
    COUNT(DISTINCT l.les_id) AS TotalLes
FROM teachers t
LEFT JOIN students s ON s.std_tch_id = t.tch_id
LEFT JOIN lessons  l ON l.les_tch_id = t.tch_id
GROUP BY t.tch_id, t.tch_name
ORDER BY t.tch_id;

In this query:

• We join the teachers, students, and lessons tables on their respective foreign keys.
• We use a LEFT JOIN to include teachers without any students or lessons.
• We group the results by both tch_id and tch_name to ensure accurate counting of students and lessons for each teacher.
• We use COUNT(DISTINCT) to eliminate duplicate counts, ensuring that we only count each student and lesson once.

How It Works

The query works by first joining the three tables together. For each row in the teachers table, it joins with both the students and lessons tables using the respective foreign keys. This results in a Cartesian product of the students and lessons tables for each teacher.

Next, we group the results by both tch_id and tch_name. This ensures that we count the students and lessons separately for each teacher.

Finally, we use COUNT(DISTINCT) to compute the counts of students and lessons. Since we’re counting distinct values, this eliminates any duplicate counts.

Performance Considerations

While using an aggregated JOINed query can provide better performance than inline subqueries, it ultimately depends on your schema and data.

In general, if you have a large number of teachers with many students and lessons, the original query may still perform well. However, as the dataset grows, the aggregated query will likely outperform the original query due to its optimized join order and reduced use of subqueries.

Best Practices

Here are some best practices for writing effective queries like this:

• Always prefix field names with table aliases to improve readability.
• Use COUNT(DISTINCT) when counting distinct values to avoid duplicates.
• Avoid using inline subqueries when possible, as they can negatively impact performance.
• Consider using an aggregated query when you need to join multiple tables together.

Conclusion

In this article, we explored a complex query problem involving multiple related tables. We examined the original query and its limitations before presenting an alternative approach using an aggregated JOINed query with COUNT(DISTINCT). By following best practices for writing effective queries, you can improve performance, readability, and maintainability of your database applications.

Additional Considerations

There are several additional considerations when working with related tables:

• Use foreign keys to establish relationships between tables.
• Normalize data by eliminating redundant information in separate tables.
• Regularly review and update your schema to ensure optimal query performance.

By understanding these concepts and best practices, you’ll be better equipped to tackle complex database challenges and write efficient queries that meet the needs of your applications.

Last modified on 2024-05-04