在 PostgreSql 中计算百分比 [英] Calculating percentage in PostgreSql

问题描述

我有一个这样的表格，例如:

I have a table like this for example:

string adm
A       2
A       1
B       2
A       1
C       1
A       2

通过一个 SQL 查询，我想要这样的东西:

And through a SQL query I want something like this:

string    perc_adm (%)
A            50
B            100
C            0

我想要每个字符串中第 2 次出现的百分比.我可以在不同的条件下获得它，但我只需要一个条件.我也有一些除以零错误.在这种情况下我该如何纠正?

I want the percentage of number 2 occurrence in each string. I am able to get this in separate conditions but I need just one condition with it. Also I have some divided by zero error. How do I correct that in the condition?

推荐答案

尝试:

select 
    string,
    (cnt_2/total::float)*100 perc_adm
from (
    select 
        string, 
        count(*) total, 
        sum(case when adm = 2 then 1 else 0 end) cnt_2
    from tbl
    group by string
) x
order by string

这里total::float将除法转换为float值，否则int/int ==>int.

Here total::float convert the division into a float value, else int/int ==>int.

sql 小提琴演示

这篇关于在 PostgreSql 中计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！