mysql - MySQL 中的简单百分比计算

Question

我正在尝试在 MySQL 中运行一个简单的百分比计算。这看起来很简单，但显然这里有些地方不对。

Select state, job_type, count(*) cnt, (count(*)/(SELECT SUM(count(*)) FROM flow.job)) * 100 AS pct
From flow.job J
Where start_date >= '2018-12-14'
Group by state, job_type

Error Code: 1111.invalid use of group function

如果查询稍微复杂一些，因为我将两个表连接在一起，那会是什么样子？我正在尝试这个。

select DR.state, count(*) cnt, (count(*)/(SELECT SUM(count(*)) FROM flow.run DR, flow.job J where DR.id = J.id)) * 100 AS pct
from flow.run DR, flow.job J
where DR.id = J.id
and DR.start_date > '2018-12-08'
group by DR.state

我遇到了同样的错误...

Error Code: 1111.invalid use of group function

Answer 1

正如其他人评论的那样，您不能在 SQL 中执行 SUM(COUNT(*))。

我假设您希望百分比是日期范围内每个组的计数占同一日期范围内所有组总数的百分比。

我会将总数计算为 FROM 子句中的子查询，因此它会执行一次子查询并重用其结果。

SELECT DR.state, COUNT(*) AS count, COUNT(*) / total.count AS pct
FROM flow.run AS DR
JOIN flow.job AS J ON DR.id = J.id
CROSS JOIN (
  SELECT COUNT(*) AS count 
  FROM flow.run AS DR
  JOIN flow.job AS J ON DR.id = J.id
  WHERE DR.start_date > '2018-12-08'
) AS total
WHERE DR.start_date > '2018-12-08'
GROUP BY DR.state

请记住在子查询中使用相同的日期范围条件。查询无法猜测您指的是同一日期范围内在子查询中计数的行，只是因为外部查询存在条件。