如何在sql中获得两个表的2个十进制百分比这是在SQLServerManagementStudio 18中

pgpifvop 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(312)

我想得到所有网站的百分比。。。我试过加州大学旧金山分校，但错了

SELECT A.Year
    ,A.Month
    ,(sum(A.BCHO) * 100 / NULLIF((sum(B.BCHO)), 0)) AS BCHO
    ,
    --SELECT FORMAT((37.0/38.0),'P3') as [ThreeDecimalsPercentage]
    FORMAT((sum(A.UCSF)) * 100 / (sum(B.UCSF)), 'P2') AS UCSF_P
    ,-------------------This is wrong
    (sum(A.UCSF) * 100 / (sum(B.UCSF))) AS UCSF
    ,(sum(A.UCLA) * 100 / (sum(B.UCLA))) AS UCLA
    ,(sum(A.UCD) * 100 / (sum(B.UCD))) AS UCD
    ,(sum(A.UCI) * 100 / (sum(B.UCI))) AS UCI
    ,(sum(A.UCSD) * 100 / (sum(B.UCSD))) AS UCSD
FROM Denials_Scrub_Final_V A
INNER JOIN Claims_Final_V B ON B.Year = A.Year
    AND A.Month = B.Month
GROUP BY A.Year
    ,A.Month
    ,A.UCSF
    ,B.UCSF
ORDER BY A.Year
    ,A.Month

sql sql-server Percentage

来源：https://stackoverflow.com/questions/62958188/how-to-get-2-decimal-percentage-in-sql-of-two-tables-this-is-in-sql-server-manag

2条答案

按热度按时间

d5vmydt91#

我就是这样格式化的。。。

SELECT 
A.Year,
A.Month,
FORMAT((sum(A.BCHO)*100/NULLIF((sum(B.BCHO)),0))/100.0,'P') as BCHO,  
FORMAT((sum(A.UCSF))*100.0/(sum(B.UCSF))/100.0,'P') as UCSF,
FORMAT((sum(A.UCD))*100.0/(sum(B.UCD))/100.0,'P') as UCD,
FORMAT((sum(A.UCLA))*100.0/(sum(B.UCLA))/100.0,'P') as UCLA,
FORMAT((sum(A.UCI))*100.0/(sum(B.UCI))/100.0,'P') as UCI,
FORMAT((sum(A.UCSD))*100.0/(sum(B.UCSD))/100.0,'P') as UCSD
FROM Denials_Scrub_Final_V A
INNER JOIN  Claims_Final_V B
ON B.Year = A.Year
AND A.Month = B.Month
GROUP BY
A.Year,
A.Month,
A.UCSF,
B.UCSF
ORDER BY
A.Year,
A.Month

赞(0）回复(0）举报 2021-07-29

2exbekwf2#

sql server执行整数除法。要获取小数位，请使用 100.0 而不是 100 :