0

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...

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.