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.