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

Answer 2

SELECT
    date AS 'Date',
    group_name AS 'Group Name',
    total_answered AS 'Total Answered',
    CASE WHEN group_name LIKE '%Voice Mail' THEN
    CAST(total_answered AS FLOAT) /
    (
        SELECT SUM(total_answered)
        FROM t AS t2
        WHERE t2.date = t1.date AND t2.group_name LIKE SUBSTR(t1.group_name, 1, 6) || '%'
    )
    END AS 'VM%'
FROM t AS t1;

Answer 3

select *,sum (case when group name='team 1 voice mail' then total answered else null end) over (partition by date,group name)/sum (1) over (partition by date,group name) as vm;

Answer 4

其实特别不建议在sql中做处理，效率慢，增加sql复杂度而且不雅观，不易维护。查出结果直接在代码中运算出结果返回更合适

Answer 5

我看到正确答案就不写了，那些开窗虽然简单点，但你用不了；
使用参数得挺适合得

Answer 6

你的Team1 和 Team1 Voice Mail 之间有关系吗？这个很重要哦