Calculate multiple values with sql with if expression

Question

I have a booking table with 6 columns called booked_start, booked_stop, used_start, used_stop, invoice_start, invoice_stop. The values are floats. I want to get a sum of the rows that has values above 0 but i also want it to calculate say used_stop - used_start.

Currently im working with this:

SELECT
    room,
    IF( booked_stop_time > 0, sum(booked_stop_time - booked_start_time), 0 ) as booked,
    IF( used_stop_time > 0, sum(used_stop_time - used_start_time), 0 ) as used,
    IF( invoice_stop_time > 0, sum(invoice_stop_time - invoice_start_time), 0 ) as invoice
FROM bookings

The problem is that if expr1 returns false it'll reset the sum. I only want to add the rows value to the sum if its above 0.

I also tried using a case but that didnt really work. Maybe i should do the calculation in php instead?

Answer 1

This should work:

SELECT
  room,
  SUM(
    CASE WHEN booked_stop_time - booked_start_time > 0
      THEN booked_stop_time - booked_start_time
      END
  ) AS booked,
  SUM(
    CASE WHEN used_stop_time - used_start_time > 0
      THEN used_stop_time - used_start_time
      END
  ) AS used,
  SUM(
    CASE WHEN invoice_stop_time - invoice_start_time > 0
      THEN invoice_stop_time - invoice_start_time
      END
  ) AS invoice
FROM bookings

Focusing on the booked value:

• If booked_stop_time - booked_start_time is greater than zero the CASE returns booked_stop_time - booked_start_time, so it's included in the sum.
• The CASE doesn't have any other conditions, so if booked_stop_time - booked_start_time is not greater than zero, the CASE returns NULL, which means the row is not included in the sum.