gpt4 book ai didi

mysql - MySQL 中的百分比

转载 作者:行者123 更新时间:2023-12-01 00:33:33 24 4
gpt4 key购买 nike

使用这个查询,

SELECT company, YEAR( date )  as year, COUNT( * ) as total
FROM table
WHERE company = "Medtronic"
OR company = "Private"
GROUP BY YEAR( date )

我得到一个这样的表:

Company         year    total
Medtronic 1998 6
Private 1998 5
Medtronic 1999 5
Private 1999 1

如何计算每家公司每年贡献的百分比?

例如,美敦力在 1998 年的贡献百分比是6/(6+5) = 54.5%

我一直在尝试通过 MySQL 查询来计算百分比。

谢谢你们。

最佳答案

使用:

SELECT x.company,
x.year,
x.annual_total
x.annual_total / y.total AS percentage
FROM (SELECT t.company,
YEAR(t.date) as year,
COUNT( * ) as annual_total
FROM TABLE t
WHERE t.company IN ('Medtronic', 'Private')
GROUP BY YEAR( t.date ) ) x
JOIN (SELECT t.company,
COUNT(*) 'total'
FROM TABLE t
WHERE t.company IN ('Medtronic', 'Private')
GROUP BY t.company) y ON y.company = x.company

如果您想要带有特定小数位的百分比,请使用:

CAST(x.annual_total / y.total AS DECIMAL(2,2)) AS percentage

检查这是否给出了您期望的每个公司的计数:

  SELECT t.company,
COUNT(*) 'total'
FROM TABLE t
WHERE t.company IN ('Medtronic', 'Private')
GROUP BY t.company

关于mysql - MySQL 中的百分比,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1878469/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com