MySQL 5.6 – Calculate Percentage Difference Between Two Columns

MySQLmysql-5.6

The calculation formula for difference between 2 numbers should be:

enter image description here

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…

Thank you in advance for any reply…

Best Answer

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
Related Question