SQL需求：获取所有Distinct Class并仅对计数应用Where条件

Got it, this is a common SQL scenario where you want to keep every unique class in your results, but only count rows that meet specific criteria for each class. Here are two reliable approaches depending on your needs:

Approach 1: Use COUNT() with a CASE Statement #

This is the most straightforward method if your filter condition is simple. Instead of applying a WHERE clause to the entire query (which would filter out non-matching classes), use a CASE inside the COUNT() function to only count rows that satisfy your condition. Rows that don’t meet the condition will return NULL, and COUNT() ignores NULL values entirely.

Example query:

SELECT 
    class,
    COUNT(CASE WHEN class = 'First' THEN 1 END) AS filtered_count
FROM your_table
GROUP BY class;

Breakdown: #

• GROUP BY class ensures every distinct class appears in your results.
• The CASE statement returns 1 only when the row matches your condition (here, class = 'First'), otherwise it returns NULL.
• COUNT() tallies only non-null values, so classes with no matching rows will show a count of 0.

Approach 2: Use a CTE with LEFT JOIN #

If your filter involves multiple columns (like date ranges, status flags, etc.) and you want more flexibility, use a Common Table Expression (CTE) to first get all distinct classes, then left join to a subquery that calculates the filtered counts. This guarantees you retain every class even if it has no matching rows in the filtered subset.

Example query:

WITH all_distinct_classes AS (
    SELECT DISTINCT class FROM your_table
)
SELECT 
    adc.class,
    COALESCE(filtered_counts.count, 0) AS filtered_count
FROM all_distinct_classes adc
LEFT JOIN (
    SELECT class, COUNT(*) AS count
    FROM your_table
    -- Add your full filter condition here (supports multiple columns)
    WHERE class = 'First' AND created_date >= '2024-01-01'
    GROUP BY class
) filtered_counts ON adc.class = filtered_counts.class;

Breakdown: #

• The CTE all_distinct_classes grabs every unique class from your table.
• The subquery filtered_counts calculates the count of rows that meet your full condition.
• LEFT JOIN ensures all rows from the CTE are kept, even if there’s no match in the filtered subquery.
• COALESCE replaces NULL (for classes with no matching rows) with 0 to make the output cleaner.

Example Output #

Suppose your table has classes 'First', 'Second', 'Third', and your condition is class = 'First'. The output would look like this:

This matches exactly what you’re looking for—all distinct classes, with counts only reflecting the rows that meet your specified condition.

内容的提问来源于stack exchange，提问作者KrishNa Kumar