根据查询结果计算百分比 [英] calculating a percentage based on results from the query

问题描述

我当前正在运行查询:

select table1.columnA as Barrier_1,
       table2.columnB as Action_1,
  from table2
  join table1 on table2.PrimKey = table1.PrimKey
 where table1.columnA is not null
   and table2.columnB is not null
 group by table1.columnA, table2.columnB
 order by table1.columnA

返回表:

Barrier_1   Action_1
____________________
   01     |    01
   01     |    02
   02     |    01
   02     |    03
   02     |    04
   03     |    02
   03     |    03
   04     |    01
   05     |    04

我想做的是计算每个障碍的每个动作所占的百分比:

and what I want it to do is calculate the percentage of each action per barrier:

Barrier_1   Action_1   Percent_1
________________________________
   01     |    01    |   60%
   01     |    02    |   40%
   02     |    01    |   20%
   02     |    03    |   10%
   02     |    04    |   70%
   03     |    02    |   20%
   03     |    03    |   80%
   04     |    01    |  100%
   05     |    04    |  100%

请注意，每个动作每个障碍可以显示多次.

Note that each action can show up multiple time per barrier.

因此，每个障碍都有其自己的一套行动.例如，障碍一共有5个动作(2个是动作02，3个是动作01).

So each barrier has it's own set of actions. For example, barrier one can have a total of 5 actions (2 being action 02 and 3 being action 01).

推荐答案

您可以使用子查询来获取一个Barrier的所有Actions的总和.然后计算一个Barrier的Actions百分比.

You can use a sub-query for getting sum of all Actions for one Barrier. And then calculate percentage of Actions for one Barrier.

SELECT b.ColumnA
, a.ColumnB
, ROUND(CONVERT(DECIMAL(19,4),
                COUNT(a.ColumnB))* 100/
CONVERT(DECIMAL(19,4),
        (SELECT COUNT(ColumnB) FROM Action WHERE PrimKey=b.PrimKey))
, 2) AS Pros
FROM Action a
INNER JOIN Barrier b ON b.PrimKey=a.PrimKey
GROUP BY b.ColumnA, a.ColumnB, b.PrimKey
ORDER BY b.ColumnA

这里有一个 SQL提琴的链接，其中包含我用于测试的数据...

Here a link to SQL Fiddle with data which I used for testing...

如果要在结果列中添加'％'符号，则需要将百分比值转换为VARCHAR并添加符号.

If your want to add a '%'-sign to result column, then you need to convert value of percentage to VARCHAR and add a sign.

CONVERT(VARCHAR(20), @PercentageValue) + '%' AS PercentageValue

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