Search code examples
mysqlsubtraction

subtract 2 DB rows from each other for each unique partner_ID? MYSQL

I have a MYSQL table where I need to get to subtract values from 2 different rows.

This is my DB table:

Tablename: ext_partnertotals

| Partner_ID | Partnername | Month | Year | Total |
|------------|-------------|-------|------|-------|
| 1          | Partner 1   | 1     | 2018 | 10    |
| 1          | Partner 1   | 2     | 2018 | 12    |
| 2          | Partner 2   | 1     | 2018 | 18    |
| 2          | Partner 2   | 2     | 2018 | 12    |

It should get this with a query:

| Partner_ID | Partnername | up/down |
|------------|-------------|---------|
| 1          | Partner 1   | +2      |
| 2          | Partner 2   | -6      |

I need to get the Subtract value of 2 different months for each Partner.
Every Partner has a tablerow for each month and a value for that month.
Now I need to get If they went up or went down in value since the month before.
Can someone write me a query?

Solution

  • Since you're unable to improve your terrible schema, I recommend you use a (very ugly/hard to maintain and very slow) correlated subquery:

    SELECT Partner_ID, Partnername, Year, Month, Total - (
  SELECT Total
  FROM   ext_partnertotals AS prev
  WHERE  prev.Partner_ID = cur.Partner_ID AND CASE cur.Month
           WHEN 1 THEN prev.Year = cur.Year - 1 AND prev.Month = 12
           ELSE prev.Year = cur.Year AND prev.Month = cur.Month - 1
         END
) AS `up/down` FROM ext_partnertotals AS cur

    See it on sqlfiddle.