获取返回行值的百分比

e5nszbig 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(277)

希望获得每个“任务类型”的百分比值。看了很多例子，但不太明白。我尝试将下面的语句放入另一个select语句中以尝试分割，但它总是有sql错误。有什么想法吗？
以下是sql：

SELECT t.task_type, (SUM(trktm.time_end) - SUM(trktm.time_start)) as thetime
   FROM
   task as t, track_time as trktm
   WHERE
   trktm.time_start BETWEEN 1593932400 AND 1594450800
   AND
   trktm.time_end BETWEEN 1593932400 AND 1594450800
   AND
   t.user_id = 10
   GROUP BY t.task_type
   ORDER BY FIELD(t.task_type, 'Unassigned') DESC, t.task_type ASC

输出如下：

task_type   |  thetime
- - - - - - - - - - - - - -         
Unassigned  |  1041078
Bug Fixes   |  694052
Design      |  347026

但我要达到的目标是：

task_type   |  thetime   |   thepercent
- - - - - - - - - - - - - - - - - - - - -         
Unassigned  |  1041078   |   70.4
Bug Fixes   |  694052    |   19.6
Design      |  347026    |   10.0

sql mysql sum Percentage

来源：https://stackoverflow.com/questions/62828539/get-percentage-of-returned-row-values

1条答案

按热度按时间

b0zn9rqh1#

直截了当的解决办法：加入totaltime

SELECT taskt_type, thetime, (thetime/totaltime) * 100 as thepercent From 
  (SELECT t.task_type, (SUM(trktm.time_end) - SUM(trktm.time_start)) as thetime
     FROM
       task as t, track_time as trktm
     WHERE
       trktm.time_start BETWEEN 1593932400 AND 1594450800
     AND
       trktm.time_end BETWEEN 1593932400 AND 1594450800
     AND
      t.user_id = 10
   GROUP BY t.task_type) A
JOIN 
  (SELECT  (SUM(trktm.time_end) - SUM(trktm.time_start)) as totaltime
     FROM
       task as t, track_time as trktm
     WHERE
       trktm.time_start BETWEEN 1593932400 AND 1594450800
     AND
       trktm.time_end BETWEEN 1593932400 AND 1594450800
     AND
      t.user_id = 10
   ) B
ORDER BY FIELD(task_type, 'Unassigned') DESC, task_type ASC

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

我来回答

获取返回行值的百分比

1条答案

相关问题

热门标签

最新问答