SQL GROUP BY查询问题：统计Bucket中有无数据的ID数量及总量

Got it, let's get this sorted out. The core problem with your current SQL is that your grouping and conditional counting logic isn't aligned with what you need to calculate—you're grouping by Date and Data, which splits your results into tiny subsets instead of aggregating at the Bucket level, and your case statements aren't properly accumulating the counts for each category.

Correct Query Solution #

Here's the adjusted query that will give you the exact metrics you're after:

SELECT
    Buckets,
    -- Count distinct IDs that have data (Date is not null)
    COUNT(DISTINCT CASE WHEN Date IS NOT NULL THEN Data END) AS TRUE_Vol,
    -- Count distinct IDs that have no data (Date is null)
    COUNT(DISTINCT CASE WHEN Date IS NULL THEN Data END) AS FALSE_Vol,
    -- Total distinct IDs in the bucket (sum of TRUE_Vol and FALSE_Vol)
    COUNT(DISTINCT Data) AS Total_Vol
FROM #temptable
GROUP BY Buckets;

How This Works #

Let's break down each part to make it clear:

• TRUE_Vol: The CASE statement only returns the Data (ID) value when Date isn't null. COUNT(DISTINCT) ignores any NULL results from the case, so we end up with the count of unique IDs that have associated data.
• FALSE_Vol: This does the opposite—only returns IDs where Date is null, then counts the distinct values to get the number of IDs with no data.
• Total_Vol: Instead of manually adding TRUE_Vol and FALSE_Vol (which would work too), we can directly count all distinct IDs in the bucket for a cleaner, more efficient calculation.

Why Your Original Query Failed #

Your original query grouped by Buckets, Date, Data, which meant:

• Each unique combination of Bucket, Date, and ID was treated as a separate row.
• The case statements only returned a value for their specific condition, leading to scattered NULLs and incorrect counts instead of aggregated totals per Bucket.

This adjusted query groups only by Buckets, which ensures we're calculating all metrics at the level you need.

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