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

Optimize query design: use a single SQL statement to achieve multiple counts

In data analysis, it is often necessary to obtain multiple counts for different conditions in the table. While a simple query can return a single count, getting multiple counts in a single query requires a more nuanced approach.

Query structure:

For this, we can use CASE statement combined with aggregate functions. This method simulates the functionality of the PIVOT function in some database systems. The generated query takes the form:

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

Instructions:

The
• COUNT(*) function counts the total number of rows for each distributor_id.
The
• CASE statement uses conditional logic to increment the row count based on the level column. If level is 'exec', then ExecCount is incremented; if level is 'personal', then PersonalCount is incremented.
The
• GROUP BY clause groups the results by distributor_id, ensuring a unique count for each distributor.

Advantages of this method:

• Efficiency: This query retrieves multiple counts with a single execution, eliminating the need for multiple queries or complex subqueries.
• Clarity: The query structure is simple and easy to understand, easy to maintain and modify.
• Flexibility: CASE statements can be modified to accommodate other conditions or levels to meet future needs.

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