sql-server - 在 SQL Server 中计算总数的百分比

Question

如何计算总数的百分比，就像我们在 excel 中使用的那样将计数显示为 %。

请参考附加的屏幕截图，我需要 % 明智的显示而不是数字。

下面是我的 SQL 查询：

SELECT
[Region]=ISNULL(zone, 'Grand Total'),
[chennai]=SUM([chennai]),
[Kandivali]=SUM([Kandivali]),
[kolhapur]=SUM([kolhapur])
from
(select zone,[Ret#Location]
from [Test].[dbo].[dailyAssure_1]
) src
pivot
(count([Ret#Location])
for [Ret#Location] in ([chennai], [Kandivali], [kolhapur])
) piv
Group by ROLLUP(zone);

标签： sql-server

Answer 1

这有点像黑客，但类似的东西可能会起作用......

with 
    data as (
        -- this is your query
        SELECT  [Region]=zone,
                [chennai]=SUM([chennai]),
                [Kandivali]=SUM([Kandivali]),
                [kolhapur]=SUM([kolhapur])
        from    (select zone, [Ret#Location] from [Test].[dbo].[dailyAssure_1]) src
        pivot
        (count([Ret#Location]) for [Ret#Location] in ([chennai], [Kandivali], [kolhapur]) ) piv
        Group by ROLLUP(zone)
    ) ,
    totals as (
        -- one row with totals for chennai, etc...
        select  zone='Grand Total', 
                chennai=sum(chennai), 
                kandivali=sum(kandivali), 
                kolhapur=sum(kolhapur)
        from    data
    )
select  d.zone ,
        d.chennai / t.chennai as chennai ,
        d.kandivali/ t.kandivali as kandvali ,
        d.kolhapur / t.kolhapur as kolhapur
from    data d
cross join totals t
;

它不包括总行（无论如何都是 1），您可能需要转换 % 计算中使用的值（或 % 值本身）以获得所需的输出格式。