How to Get Multiple Counts from a Single SQL Query?

Efficiently Getting Multiple Counts with One SQL Query

Database queries often require retrieving multiple aggregate values based on different conditions. While some approaches might be inefficient, a concise method uses the CASE statement with aggregate functions, effectively mimicking a PIVOT function's capabilities in databases that lack this feature.

Here's how to do it:

Let's say you need counts categorized by different values in a column. For example, consider a table tracking distributors and their levels ('exec' or 'personal'). The following query efficiently calculates various counts:

<code class="language-sql">SELECT distributor_id,
       COUNT(*) AS total_count,
       SUM(CASE WHEN level = 'exec' THEN 1 ELSE 0 END) AS exec_count,
       SUM(CASE WHEN level = 'personal' THEN 1 ELSE 0 END) AS personal_count
FROM yourtable
GROUP BY distributor_id;</code>

This query performs the following:

• *`COUNT()**: Calculates the total number of rows for eachdistributor_id`.
• SUM(CASE WHEN level = 'exec' THEN 1 ELSE 0 END): Counts rows where level is 'exec'. The CASE statement assigns 1 if the condition is true, 0 otherwise. SUM then totals these 1s and 0s.
• SUM(CASE WHEN level = 'personal' THEN 1 ELSE 0 END): Similarly, counts rows where level is 'personal'.
• GROUP BY distributor_id: Groups the results, providing separate counts for each distributor.

This single query efficiently returns the total count and separate counts for 'exec' and 'personal' levels for each distributor_id, all in a single result set. This approach is far more efficient than running multiple separate queries.

The above is the detailed content of How to Get Multiple Counts from a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!