jeudi 30 juin 2016

mysql for percentage between rows

I have some sql that looks like this:

SELECT 
stageName,
count(*) as `count`

FROM x2production.contact_stages
WHERE FROM_UNIXTIME(createDate) between  '2016-05-01' AND DATE_ADD('2016-08-31', INTERVAL 1 DAY) 
    AND (stageName = 'DI-Whatever' OR stageName = 'DI-Quote' or stageName = 'DI-Meeting')
Group by stageName
Order by field(stageName, 'DI-Quote', 'DI-Meeting', 'DI-Whatever')

This produces a table that looks like:

+-------------+-------+
|  stageName  | count |
+-------------+-------+
| DI-quote    |  1230 |
| DI-Meeting  |   985 |
| DI-Whatever |   325 |
+-------------+-------+

Question:

I would like a percentage from one row to the next. For example the percentage of DI-Meeting to DI-quote. The math would be 100*985/1230 = 80.0%

So in the end the table would look like so:

+-------------+-------+------+
|  stageName  | count | perc |
+-------------+-------+------+
| DI-quote    |  1230 | 0    |
| DI-Meeting  |   985 | 80.0 |
| DI-Whatever |   325 | 32.9 |
+-------------+-------+------+

Is there any way to do this in mysql?

Here is an SQL fiddle to mess w/ the data: http://sqlfiddle.com/#!9/61398/1

Aucun commentaire:

Enregistrer un commentaire