nvarchar的SQL divide问题

Question

我有简单的table1

Catergory
 A
 A
 A
 B
 B

每组需要百分比(A组=3 (a)/5 (A和B合计)，与B组相同

percent Catergory
 .6       A
 .4       B

我的查询会有问题

将nvarchar值'A‘转换为数据类型int时转换失败

with total as
    (select count(catergory) as totalcount, catergory as single
    from table2
    group by catergory )

select single/ totalcount from total

你能纠正这个问题吗？谢谢。

Answer 1

您可以使用窗口函数来执行以下操作：

select
    1.0 * count(*) / sum(count(*)) over() ratio,
    category
from mytable
group by category