How to Get Multiple Counts With One SQL Query?

In database management efficiency is key. SQL operations from data retrieval to manipulation, need to be optimized to ensure smooth processing. One common requirement is the need to obtain multiple counts from the table based on different conditions. While it’s logical to execute separate queries, SQL provides a more elegant solution of obtaining multiple counts with just one query which ensures Faster Development, Improved Performance, Better Query Optimization, and many more such advantages.

This article explores techniques for obtaining multiple counts with a single SQL query, offering insights into data retrieval processes and improving overall performance.

COUNT ()

The COUNT() function returns the total number of rows that match our given conditions. It is one of the aggerate functions in SQL which provides a powerful mechanism for performing conditional logic and allows you to perform calculations on groups of rows, making them essential for obtaining counts.

Syntax:

SELECT COUNT(column_name)

FROM table_name

WHERE condition;

SELECT COUNT(*)

FROM table_name;

Here (*) with COUNT returns all the count of rows from the table.

SELECT COUNT(CASE WHEN condition THEN column_name END)

FROM table_name;

This syntax is used when we want to count the rows with specified condition is true within the specified column, this is mainly used to get multiple counts.

Example of Multiples Count Queries in SQL

Example 1: Counting Books in a Library Database with Multiple Conditions in a Single Query

Let us take an example of Library database. Where there is a table called Books which has columns such as book_id, title, author_id, genre_id, publication_year.

Creating our table Books:

CREATE TABLE Books ( book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author_id INT,
    genre_id INT,
    publication_year INT);

Insert values in our table:

INSERT INTO Books VALUES
    (1, 'To Kill a Mockingbird', 101, 201, 1960),
    (2, '1984', 102, 202, 1949),
    (3, 'Pride and Prejudice', 102, 203, 1813),
    (4, 'The Great Gatsby', 104, 204, 1925),
    (5, 'The Catcher in the Rye', 105, 205, 1951);

Display our table:

SELECT * FROM Books;

Output:

Books Table

1. Now if we want to get total number of books which where published in year 1900’s.

SELECT COUNT( * ) AS total_books
FROM Books
WHERE publication_year LIKE '19%';

Output:

total_books output

Explanation:

In this query, the COUNT() function is returns the number of books published in the 1900s. This is achieved by employing the LIKE operator to scan which books were published in years containing the digits `19`.

2. Now if we want total number of books with author id as 102.

SELECT COUNT( * ) AS total_author_id
FROM Books
WHERE author_id = 102;

Output:

total_author_id output

Explanation:

The above query returns the total count of books which has author id as 102 from the Books table.

3. How to get total number of books with author id as 102 and genre id as 202 which where published in year 1900’s together in one query.

SELECT COUNT( CASE WHEN publication_year LIKE '19%' THEN 1 END ) AS total_books,
COUNT( CASE WHEN author_id = 102 THEN 1 END ) AS total_author_id,
COUNT( CASE WHEN genre_id = 202 THEN 1 END ) AS total_genre_id
FROM Books;

Output:

Explanation:

In the above query the COUNT(CASE WHEN publication_year LIKE ‘19%’ THEN 1 END) , This counts the number of books which where publication year starts with ’19’. It uses a CASE statement to check if the publication year satisfies the condition, and increments the count by 1 if it does. The result is aliased as total_books.

COUNT(CASE WHEN author_id = 102 THEN 1 END) , This counts the number of books by a specific author with author id is equal to 102. It uses a similar CASE statement to check if the author_id matches the condition, and increments the count by 1 if it does, the result is aliased as total_author_id, and same with COUNT(CASE WHEN genre_id = 202 THEN 1 END) checks if the genre_id matches the condition, and increments the count by 1 if it does then result it as total_genre_id.

The example demonstrates the concept of utilizing a single SQL query with different conditions to obtain multiple results in one go. By employing this approach, rather than executing separate queries for each specific condition and repeatedly accessing the database table, we use multiple counts within single query and got desired output.

Note:

The CASE statement in combination with COUNT can be used to count values based on specified criteria. This is helpful when you want to count different queries within a single query, returns 1 for rows that satisfy the condition and NULL for those that don’t satisfy.

Example 2: Counting Citizens Without Voting Cards in a Single SQL Query

Let us take another table for more understanding

We have a table which contains data of a particular group of people who are citizens of a country in which there are people of different age and contains the citizens who are eligible or not for voting and whether they has voting cards if eligible. These tables contains columns like id, name, age and has_voting_card.

citizens table

1. From above Citizens Table we need to find the total number of people who are eligible for voting but don’t have voting cards.

SELECT COUNT(CASE WHEN age >= 18 AND has_voting_card = 0 THEN 1 ELSE NULL END) AS eligible_citizens_without_card
FROM Citizens;

Output:

eligible_citizens_without_card output

This query gives us total number of citizens who are eligible for voting aged 18 or above and also don’t have voting cards, this is also an example of multiple count in single query we use CASE statement which conditionally count based on the specified condition we have provided.

Conclusion

Efficiency is very important in database management, and SQL’s aggregate functions provide a powerful toolset for optimizing data retrieval operations. By leveraging function like COUNT() , we can efficiently obtain multiple counts within one SQL query. This approach not only well organizes our code but also improves performance, readability, and makes it time saving.