Sql server中如何在case when 里面做除法，分母是另一个sub query

Question

大家好，请大家帮忙一下这一题：

第一个表是我现在的数据，第二个表是我想要的结果。我想要加多一列计算这个VM%，这个VM%=Voice Mail 的数量除以这一个组总共接听数。例如Team 1在9/2 号的VM%=3/(22+3), Team 2 在9/2 的VM%=7/(101+7)。以此类推。软件用到是sql server。谢谢！！

Answer 1

--三楼的改一下
DECLARE @a TABLE([Date] DATE,[Group Name] VARCHAR(50),[Total Answered] INT)

INSERT @a SELECT '9/2/2022','Team 1',22
UNION ALL SELECT '9/2/2022','Team 1 Voice Mail',3
UNION ALL SELECT '9/2/2022','Team 2',101
UNION ALL SELECT '9/2/2022','Team 2 Voice Mail',7
UNION ALL SELECT '9/6/2022','Team 1',55
UNION ALL SELECT '9/6/2022','Team 1 Voice Mail',2
UNION ALL SELECT '9/6/2022','Team 2',100
UNION ALL SELECT '9/6/2022','Team 2 Voice Mail',3

SELECT
    [Date],
    [Group Name],
    [Total Answered],
    CASE WHEN CHARINDEX('Mail',[Group Name])>0 THEN
    CONVERT(REAL,[Total Answered]) /
    (   SELECT SUM([Total Answered])
        FROM @a 
        WHERE [Date] = t1.[Date] AND [Group Name] LIKE LEFT(t1.[Group Name],6) + '%'
    )
    END 'VM%'
FROM @a t1;

--result
2022-09-02    Team 1    22    NULL
2022-09-02    Team 1 Voice Mail    3    0.12
2022-09-02    Team 2    101    NULL
2022-09-02    Team 2 Voice Mail    7    0.06481481
2022-09-06    Team 1    55    NULL
2022-09-06    Team 1 Voice Mail    2    0.03508772
2022-09-06    Team 2    100    NULL
2022-09-06    Team 2 Voice Mail    3    0.02912621