如何为单列中的多类值添加多条件统计并将结果展示在不同列中？

Hey there! Let's figure out how to get that aggregated result you're looking for. You need to summarize your data to show total occurrences, failed counts, and success counts for each unique number—here's a straightforward SQL approach:

Core Query #

SELECT 
    number,
    COUNT(*) AS total_occurence,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_count,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count
FROM 
    your_table_name  -- Replace this with your actual table name
GROUP BY 
    number
ORDER BY 
    number;

How It Works #

Let's break down each part so you understand what's happening:

• COUNT(*): This counts every row for each grouped number—that's your total occurrence count.
• SUM(CASE...): We use a conditional CASE statement here. For rows where status is 'failed', we return 1; otherwise 0. Summing these values gives us the total number of failed entries for each number. The same logic applies to success_count.
• GROUP BY number: This ensures all calculations are grouped by each unique number instead of returning a single aggregate for the whole table.
• ORDER BY number: This is optional, but it sorts your results by number to make the output cleaner and easier to read.

Alternative Approach (Using COUNT with CASE) #

If your SQL dialect supports it, you can also use COUNT instead of SUM for the status counts. Since COUNT ignores NULL values, we can omit the ELSE clause (which defaults to NULL):

SELECT 
    number,
    COUNT(*) AS total_occurence,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) AS failed_count,
    COUNT(CASE WHEN status = 'success' THEN 1 END) AS success_count
FROM 
    your_table_name
GROUP BY 
    number
ORDER BY 
    number;

Both queries will return exactly the output you specified:

内容的提问来源于stack exchange，提问作者Kamran Ahmed