SQL Conditional Aggregate需求：按商品汇总数量并展示最新日期

Hey there! Let's walk through solving this SQL problem. First, let's recap your source items table clearly:

Your goal is to group rows by the items field, calculate a conditional sum of count (based on your expected output, we add good counts and subtract bad ones), and pull the most recent date for each item group.

Here's the SQL query using conditional aggregation that gets you exactly the result you want:

SELECT
    items,
    SUM(CASE 
        WHEN status = 'good' THEN count 
        WHEN status = 'bad' THEN -count 
        ELSE 0 
    END) AS count,
    MAX(date) AS date
FROM items
GROUP BY items
ORDER BY items;

Let's break down how this works: #

• Conditional Sum: The SUM(CASE ...) clause handles the status-based aggregation. For good entries, we add the count value; for bad entries, we subtract it (using -count). The ELSE 0 ensures any unexpected status values don't mess with the total.
• Latest Date: MAX(date) grabs the most recent date from all rows in each items group—perfect for getting the latest entry date per item.
• Grouping: GROUP BY items ensures we get one summary row for each unique item.

When you run this query, you'll get your desired output:

If you ever need to sum all counts regardless of status (instead of subtracting bad ones), you can simplify the sum to just SUM(count)—but based on your expected result, the conditional version is exactly what you need.

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