MySQL中的百分比 [英] Percentages in MySQL

问题描述

使用此查询

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

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

How do I calculate the % that is contributed by each company for each year?

例如，美敦力公司在1998年贡献的百分比为 6/(6 + 5)= 54.5％

For example, the percentage contributed by Medtronic in year 1998 is 6 / (6+5) = 54.5%

我一直在尝试进行MySQL查询以计算百分比.

I have been trying to make a MySQL query to calculate the percentages.

谢谢大家.

推荐答案

使用:

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

如果您希望百分比具有特定的小数位数，请使用:

If you want the percentage with particular decimal places, use:

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

检查并确定您期望的每个公司的数量:

Check that this gives the count per company you expect:

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

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