How Can I Get Multiple Counts from a Single SQL Query?

Optimizing SQL Queries: Obtaining Multiple Counts in One Go

This article demonstrates a streamlined method for retrieving multiple counts using a single SQL query. We'll leverage the power of the CASE statement in conjunction with aggregate functions.

The strategy involves using CASE to categorize rows based on the level column and assign a count to each category. These counts are then aggregated using SUM to provide the final results.

Here's a query that achieves this, returning the data in a single row per distributor_id:

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

This approach efficiently delivers both the overall count and the individual counts for specific level values within a single query result.

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