mysql显示百分比例_MySQL中的百分比

Using this query,

SELECT company, YEAR( date ) as year, COUNT( * ) as total

FROM table

WHERE company = "Medtronic"

OR company = "Private"

GROUP BY YEAR( date )

I get a table like this:

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?

For example, the percentage contributed by Medtronic in year 1998 is

6 / (6+5) = 54.5%

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

Thanks guys.

解决方案

Use:

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