Case or IF THEN to add minus sign to values

I'm new to SQL, taking a data analysis class, and having issues trying to solve this problem - write a query to find the difference in years of population estimates (Y2016 - Y2015 in my dataset). Show all values as rounded to the hundred absolute values, unless the latest year(Y2016 in my dataset) decreased by more than 10K, then those values need to be negative.

I'm using MYSQL 6.3.9, on Mac OS X 10.12

I've got this as my join statement

    SELECT s.StateName, c.Y2016, c.Y2015, round((abs(Y2016 - Y2015)), -2) AS Difference 
    FROM StateCensus AS c
    INNER JOIN States AS s
    WHERE s.StateID = c.YearID
    ORDER BY Difference DESC;

I can't figure out how to get a CASE statement to work that would go in the Difference column.

    SELECT Y2016, Y2015, round((Y2016 - Y2015), -2) as Test,
      CASE
          WHEN Y2015 >= (Y2016 + 10000) THEN round((Y2016 - 2015), -2)
          ELSE round((abs(Y2016 - Y2015)), -2)
      END as Difference
    FROM StateCensus;

I added the Test column to see what actual values were for testing purposes. And I know I need to alias the YXXX columns once I put it into the main query. I'm just trying to figure out how to get the correct values for now.

When I run it with the above CASE, there should be 3 rows that need to be negative. The Test column gives correct values, and the Difference column gives the correct values for all but the 3 negative rows. I'm not sure what it's doing in the difference column. This is one of the 3 rows that should be negative.

    Y2016      Y2015      Test     Difference
    12801539   12839047   -37500   -12799500

I also tried

    SELECT Y2016, Y2015, round((Y2016 - Y2015), -2) as Test,
      CASE
          WHEN (Y2016 - Y2015) >= -10000 THEN round((abs(Y2016 - 2015), -2) * -1
          ELSE round((abs(Y2016 - Y2015)), -2)
      END as Difference
    FROM StateCensus;

This one is even more off with all of the Difference column having negative values and way off from what they should be. I think I'm more on the right track with my first CASE statement.

Or should I be using an IF THEN? When I tried this, I couldn't get it to work due to syntax errors.

    SELECT s.StateName, c.Y2016, c.Y2015, 
      IF(c.Y2015 >= (c.Y2016 + 10000),  SELECT -1 * round((abs(c.Y2016-c.Y2015)), -2), SELECT round((abs(c.Y2016 - c.Y2015)), -2) AS Difference
    FROM StateCensus c
    INNER JOIN States AS s
    WHERE s.StateID = c.YearID
    ORDER BY Difference DESC;

I would be so grateful, as would my scalp and the hair I've been pulling, if someone could point me in the right direction.

Solution

The case when and if syntax are indeed two ways to do it.

In your case when attempt you had a typo: - 2015 should be - Y2015, and the comparison should be <= instead of >=. Also, you can use this construct just to determine whether to multiply with 1 or -1, without the need to repeat the round(abs(...)) expression:

CASE
    WHEN c.Y2016 - c.Y2015 <= -10000 THEN -1
                                     ELSE 1
END * round(abs(c.Y2016 - c.Y2015), -2) as Difference

In the IF syntax you turned around the condition expression, and there the >= is correct, but the select keyword should not be there. So do:

IF (c.Y2015 >= c.Y2016 + 10000, -1, 1) * round(abs(c.Y2016-c.Y2015), -2) AS Difference