# MySQL Calculate percentage difference (not change) between 2 columns

The calculation formula for difference between 2 numbers should be:

Using the above formula I need to calculate the difference in percentage between N1 and N2. Both numbers are the result of 2 separate SUM cases based on 2 ENUM values in col_type (please see below)

``````SELECT
CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N1',
CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N2'
FROM t2
INNER JOIN t1
ON t2.col_id = t1.col_id
``````

The resulted output should be like:

``````+----------+----------+------------------------+
| Total N1 | Total N2 | Diff between N1 and N2 |
+----------+----------+------------------------+
|  3765.50 |  3246.15 | 14.81%                 |
+----------+----------+-----------------------
``````

My question is how do I pass the values of N1 and N2 (already generated) to a 3rd column and calculate the difference between N1 and N2 using above formula?

Note: Please note that is percentage difference between 2 numbers and not the percentage change where (N2-N1)/N1 * 100 applies...

With some help the solution was to use a subquery something like this:

``````SELECT
CONCAT('£ ', t1.n1) AS N1,
CONCAT('£ ', t1.n2) AS N2,
CONCAT(((N1 - N2) / ((N1 + N2) / 2)) * 100, ' %') AS 'Difference'
FROM (SELECT
SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END) AS n1,
SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END) AS n2
FROM t2
INNER JOIN t1
ON t2.col_id = t1.col_id) t2
``````