How to avoid 0 values using MIN and MAX function i

Question

I have table like this:

Running    Date
  128      1/1/2018
  700      6/11/2018
  900      6/11/2018
  300      6/11/2018

If same dates then pick minimum value and maximum value and get difference from "RUNNING" Column. If only one date then do nothing and get same value

Query:

SELECT MAX(RUNNING) - MIN(RUNNING) "RUNNING", DATE
FROM TABLE
GROUP BY DATE;

I got following results:

Running    Date
  0        1/1/2018
  600      6/11/2018

But i want "128" value with "1/1/2018" date but this give 0 value

How to solve this?

Answer 1

Include a CASE statement.

SELECT
     CASE
          WHEN MAX(running) = MIN(running) THEN MAX(running)
          ELSE MAX(running) - MIN(running)
     END
"RUNNING",
     dt
FROM t
GROUP BY dt;