在Server 2008中使用百分比获取计数

Question

我在Server中计算计数，但使用该计数，我也希望在另一列中使用百分比(%)标记，因此请帮助

我要这个输出：

Total-  Resolved-   Repair -    Follow-Up-  Other
135     20          15          100         0
100%    14.81%      11.11%      74.07%      0%

我要得到计数的查询如下：

SELECT
    COUNT(*) as Total,
    (SELECT COUNT(*) FROM LMR WHERE StatusID=1) as 'Resolved',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=2) as 'Repair',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=3) as 'Follow-Up',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=4) as 'Other'
FROM LMR;

Answer 1

使用Union并将您的值转换为varchar可能如下：

SELECT CONVERT(varchar(5),COUNT(*)) as Total,
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=1)) as 'Resolved',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=2)) as 'Repair',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=3)) as 'Follow-Up',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=4)) as 'Other'
FROM LMR
UNION ALL 
SELECT CONVERT(varchar(5),COUNT(*)*100/COUNT(*))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=1)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=2)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=3)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=4)*100/COUNT(*)))+'%'
FROM LMR

Answer 2

使用Conditional count而不是为每个status执行单独的select。

计算百分比(invidual_count/total_count) * 100

尝尝这个

SELECT
    COUNT(*) as Total,
    COUNT(case when StatusID=1 then 1 end)  as 'Resolved',
    COUNT(case when StatusID=2 then 1 end) as 'Repair'
    COUNT(case when StatusID=3 then 1 end) as 'Follow-Up'
    COUNT(case when StatusID=4 then 1 end) as 'Other',
FROM LMR;
UNION ALL 
SELECT
    (COUNT(*)/COUNT(*))*100 as Total,
    (COUNT(case when StatusID=1 then 1 end)/NULLIF(COUNT(*),0))*100  as 'Resolved',
    (COUNT(case when StatusID=2 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Repair'
    (COUNT(case when StatusID=3 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Follow-Up'
    (COUNT(case when StatusID=4 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Other',
FROM LMR;