Percentages in MySQL

Question

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.

Answer 1

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

Answer 2

My SQL query:

SELECT x.company, x.year, x.annual_total, CAST( x.annual_total / y.total AS DECIMAL( 2, 2 ) ) AS percentage
FROM (

SELECT t.company, YEAR( t.date ) AS year, COUNT( * ) AS annual_total
FROM my_patents AS t
WHERE t.company = 'Private'
GROUP BY YEAR( t.date )
)x
JOIN (

SELECT t.company, COUNT( * ) AS total
FROM my_patents AS t
WHERE t.company = 'Medtronic'
OR t.company = 'Private'
GROUP BY t.company
)y ON y.company = x.company

my results:

Private  1998  5  0.04

when i run this query:

SELECT t.company, YEAR( date ) , COUNT( * ) AS total
FROM my_patents AS t
WHERE t.company = 'Medtronic'
OR t.company = 'Private'
GROUP BY t.company, YEAR( date )

I get

Medtronic  1998  6
Private  1998  5