Multiple COUNT () in a single SQL query

During the development of a site or an application it is very likely to have to count certain information with COUNT (). For example a community website can count the number of new messages and the number of notifications when the user logs in. This article explains how and why you need to perform a single query for all COUNT ().

Interest

Making several calls to a database takes a relatively long time. Calls should be reduced as much as possible. Therefore, for better performance it is advisable to avoid carrying out these 3 promptings:

SELECT COUNT(*) FROM table1;
SELECT COUNT(*) FROM table2;
SELECT COUNT(*) FROM table2 WHERE is_valid = 1;

Syntax for grouping several queries into one

The 3 queries presented below can be grouped into 1 by using the following syntax:

SELECT
(SELECT COUNT(*) FROM table1) as count1,
(SELECT COUNT(*) FROM table2) as count2,
(SELECT COUNT(*) FROM table2 WHERE is_valid = 1) as count3

This query will return a record containing the values ​​count1, count2 and count3.

Utility

Here is a small non-exhaustive list of all the features that can be counted within this single SQL query:

• Count the number of registered members
• Count the number of connected members
• Count the number of connected visitors
• Count new unread messages from a member
• Count the number of notifications for a member

Go further

To go a little more and reduce the server load, it is also possible to cache these values. But this no longer reflects SQL but the rest of the application.