TSQL中COUNT与SUM DISTINCT值对比及业务数据统计问题咨询

Hey there! I feel your pain—getting stuck on a SQL problem all day is no fun. Let's break down both the COUNT vs SUM(DISTINCT) differences and your specific customer-order-package-zone scenario so you can move forward.

These two functions serve entirely different purposes, even when paired with DISTINCT. Let's clarify each:

COUNT() (with and without DISTINCT) #

• COUNT(*): Returns the total number of rows in your result set, including rows with NULL values. Perfect for counting all records in a group.
• COUNT(column_name): Counts only the number of non-NULL values in the specified column. Ignores any rows where that column is NULL.
• COUNT(DISTINCT column_name): Counts the number of unique non-NULL values in the column. For example, if your zone column has values ['East', 'East', 'West', 'South'], COUNT(DISTINCT zone) would return 3 (only unique zones are counted).

SUM(DISTINCT) #

• SUM(DISTINCT column_name): First removes duplicate values from the specified numeric column, then sums the remaining unique values. This is very different from regular SUM(column_name), which adds every row's value regardless of duplicates.
  • Example: If seats has values [4, 4, 2, 6], SUM(DISTINCT seats) returns 12 (4+2+6), whereas SUM(seats) returns 16 (4+4+2+6).

Based on your description, you're dealing with a hierarchy: Customer → Order → Package → Zone, where each package can map to multiple zones, and you need to handle seat count statistics. Below are common T-SQL solutions for typical use cases (assuming your table is named CustomerOrderData):

Use Case 1: Count Unique Zones per Customer #

If you want to know how many distinct zones each customer has across all their orders and packages:

SELECT 
    customer,
    COUNT(DISTINCT zone) AS unique_zone_count
FROM CustomerOrderData
GROUP BY customer;

Use Case 2: Sum Seats for Unique Zones per Order #

If each zone has a fixed seat count, and you don't want to double-count seats if the same zone appears multiple times in the same order:

SELECT 
    [order],
    SUM(DISTINCT seats) AS total_unique_zone_seats
FROM CustomerOrderData
GROUP BY [order];

Important: Use DISTINCT here only if the same zone (with the same seat count) repeats in the order and you want to count its seats once. If different packages in the same order can have the same zone but different seat counts, you'll need to first aggregate at the zone level (using MAX, MIN, or AVG based on your business rules) before summing:

SELECT 
    [order],
    SUM(zone_seats) AS total_zone_seats
FROM (
    SELECT 
        [order],
        zone,
        MAX(seats) AS zone_seats -- Use MAX, MIN, or AVG depending on your needs
    FROM CustomerOrderData
    GROUP BY [order], zone
) AS ZoneLevelAggregates
GROUP BY [order];

Use Case 3: Count Unique Packages per Order #

If you also need to count how many distinct packages are associated with each order:

SELECT 
    [order],
    COUNT(DISTINCT pkg) AS unique_package_count
FROM CustomerOrderData
GROUP BY [order];

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