SQL条件计算字段逻辑异常：Count统计结果不符问题排查

Let's break down what's going wrong and how to fix your query to get the correct counts:

Key Issues in the Original Query #

1. Miscalculated Total Runners in the CTE
   Your CTE's Total Runners uses SUM(CASE WHEN td.EVENT_ID = td.EVENT_ID THEN 1 END) which always evaluates to true. Since you're grouping by EVENT_ID, BSP, WIN_LOSE, this just counts rows within each small group—not the total number of runners for the entire event. You already have the correct event-level runner count in the CountRunners subquery; that's the value you should rely on.

2. Incorrect GROUP BY and Condition References
   The outer query groups by the broken Total Runners field, and your case statements reference this group-level value instead of the event-wide total. This is why Count13to21Runners2to3 returns 0—you're filtering on group row counts instead of the actual total runners per event.

Corrected Query #

WITH CTE_TblData AS (
    SELECT 
        td.EVENT_ID, 
        td.BSP, 
        td.WIN_LOSE,
        -- Correctly get total runners for the entire event
        (SELECT COUNT(*) FROM dbo.tblData td2 WHERE td2.EVENT_ID = td.EVENT_ID) AS [TotalEventRunners],
        SUM(CASE WHEN td.WIN_LOSE = 1 THEN td.BSP END) AS [WinnerPrice],
        SUM(CASE WHEN td.WIN_LOSE = 1 THEN 1 END) AS [WinnerCount]
    FROM tblData td 
    WHERE td.EVENT_ID IN (146325086)
    GROUP BY td.EVENT_ID, td.BSP, td.WIN_LOSE
)
SELECT 
    td.EVENT_ID,
    -- Total runners in the event (each row in CTE is one runner)
    COUNT(*) AS [Total Runners],
    SUM(td.WinnerPrice) AS [WinnerPrice],
    SUM(td.WinnerCount) AS [WinnerCount],
    -- Count losing runners with BSP 13-21 in events with 1 total runner
    COUNT(CASE 
              WHEN td.BSP > 13 AND td.BSP <=21 
                   AND td.WIN_LOSE = 0 
                   AND td.TotalEventRunners = 1
              THEN td.BSP 
          END) AS Count13to21Runners0to1,
    SUM(CASE 
              WHEN td.BSP > 13 AND td.BSP <=21 
                   AND td.WIN_LOSE = 0 
                   AND td.TotalEventRunners = 1
              THEN td.BSP 
          END) AS Sum13to21Runners0to1,
    -- Count losing runners with BSP 13-21 in events with 2-3 total runners
    COUNT(CASE 
              WHEN td.BSP > 13 AND td.BSP <=21 
                   AND td.WIN_LOSE = 0 
                   AND td.TotalEventRunners BETWEEN 2 AND 3
              THEN td.BSP 
          END) AS Count13to21Runners2to3,
    SUM(CASE 
              WHEN td.BSP > 13 AND td.BSP <=21 
                   AND td.WIN_LOSE = 0 
                   AND td.TotalEventRunners BETWEEN 2 AND 3
              THEN td.BSP 
          END) AS Sum13to21Runners2to3
FROM CTE_TblData td 
WHERE td.EVENT_ID = 146325086
GROUP BY td.EVENT_ID

What Changed? #

• Renamed CountRunners to TotalEventRunners: Makes it explicit this is the total runners for the entire event, not a subgroup.
• Removed broken Total Runners calculation: We now get the event's total runners with COUNT(*) in the outer query, since each CTE row represents one runner.
• Fixed condition logic: All case statements now use TotalEventRunners (the actual event-wide count) to filter, so you're grouping stats by the event's total runner count correctly.
• Simplified GROUP BY: We only group by EVENT_ID to get clean event-level statistics, instead of including the broken group-level field.

This should now return 2 for Count13to21Runners2to3 as you expect, and keep those records out of the 0-1 interval count.

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