SQL Server查询添加合计行：合计行比率按总和计算而非求和

我在SQL Server中使用以下查询语句获取每日销售数据：

select 
    query1.date as 'Date', 
    Sold, Opportunities, 
    (Sold/Opportunities) as Ratio
from
    #query1 as query1
inner join 
    #query2 as query2 on query1.date = query2.date
order by 
    date;

当前输出为按日期排序的明细数据，每一行对应一个日期的Sold（已成交数）、Opportunities（商机数）以及当日成交比率Ratio。

我需要给结果添加一行合计行，要求：

• Sold和Opportunities列取所有行的总和
• Ratio列按SUM(Sold)/SUM(Opportunities)计算，不能直接对每日的Ratio求和

期望输出在原有明细行末尾新增一行，Date列显示“Total”，其余列按上述规则计算出合计值。

方案1：用UNION ALL拼接明细与合计行 #

这种方式逻辑直观，先查明细再单独计算合计行，最后拼接结果：

-- 查询每日明细数据
select 
    CONVERT(VARCHAR(10), query1.date, 23) as 'Date', 
    Sold, Opportunities, 
    CAST((Sold * 1.0 / Opportunities) AS DECIMAL(10,2)) as Ratio
from
    #query1 as query1
inner join 
    #query2 as query2 on query1.date = query2.date

UNION ALL

-- 计算合计行
select 
    'Total' as 'Date',
    SUM(Sold) as Sold,
    SUM(Opportunities) as Opportunities,
    CAST((SUM(Sold * 1.0) / SUM(Opportunities)) AS DECIMAL(10,2)) as Ratio
from
    #query1 as query1
inner join 
    #query2 as query2 on query1.date = query2.date

-- 确保合计行排在最后
order by 
    CASE WHEN Date = 'Total' THEN 1 ELSE 0 END, Date;

注意事项：

• 乘以1.0是为了将整数转为浮点数，避免SQL Server的整数除法导致比率被截断
• 排序时用CASE语句让合计行始终排在所有明细行之后

方案2：用GROUP BY WITH ROLLUP生成合计行（更高效简洁） #

利用SQL Server的ROLLUP分组扩展功能自动生成合计行，配合GROUPING函数识别合计行：

select 
    -- 当为合计行时，日期列显示Total，否则显示原日期
    CASE WHEN GROUPING(query1.date) = 1 THEN 'Total' ELSE CONVERT(VARCHAR(10), query1.date, 23) END as 'Date',
    SUM(Sold) as Sold,
    SUM(Opportunities) as Opportunities,
    CAST((SUM(Sold * 1.0) / SUM(Opportunities)) AS DECIMAL(10,2)) as Ratio
from
    #query1 as query1
inner join 
    #query2 as query2 on query1.date = query2.date
-- 按日期分组并生成合计行
group by 
    query1.date with rollup
-- 排序让明细在前，合计在后
order by 
    GROUPING(query1.date), query1.date;

GROUPING(query1.date) = 1表示当前行是ROLLUP生成的合计行，此时替换日期显示为“Total”

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