Oracle统计各ID的已使用SN数量问题求助

Hey, let's fix this SQL issue for you! The problem with your current query is that it either counts all non-null SNs instead of just those where USED=1, or if you tried adding a WHERE USED=1 clause, it would exclude IDs that have no matching rows (like 002 entirely).

Working SQL Solutions #

Here are two reliable ways to get every ID with their count of USED=1 entries—including those with a count of 0:

Option 1: Using SUM with a conditional case

SELECT 
    ID,
    SUM(CASE WHEN USED = 1 THEN 1 ELSE 0 END) AS USED_Count
FROM sn_table
WHERE SN IS NOT NULL AND USED IS NOT NULL
GROUP BY ID;

Option 2: Using COUNT (takes advantage of null exclusion)

Since COUNT ignores null values, we can return SN only when USED=1 (otherwise return null, which won't be counted):

SELECT 
    ID,
    COUNT(CASE WHEN USED = 1 THEN SN END) AS USED_Count
FROM sn_table
WHERE SN IS NOT NULL AND USED IS NOT NULL
GROUP BY ID;

Why Your Original Query Didn't Work #

Your original query counts all non-null SNs per ID, not just the subset where USED=1. If you tried filtering with WHERE USED=1 to target the right rows, you'd lose any IDs that have no matching entries (like 002, since all its USED values are 0).

The conditional aggregation approach fixes this: it keeps every ID in the results, checks each row, and only increments the count when USED=1—returning 0 for IDs with no matching rows.

Expected Output #

Running either query above will give you exactly the result you're looking for:

ID, USED_Count
002, 0
001, 1

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