CODEWITHSUNDEEP
mysqlsqlmath
Christian Back Kirkegaard
Christian Back Kirkegaard

Reputation: 78

Calculate multiple values with sql with if expression

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?

Upvotes: 3

Views: 328

Answers (3)

A. Zalonis
A. Zalonis

Reputation: 1609

You can try this

SELECT
    room,

    SUM( 
          IF( booked_stop_time > 0 and booked_start_time IS NOT NULL, 
                       (booked_stop_time - booked_start_time), 0 ) 
      ) AS booked,

   SUM( 
       IF(used_stop_time > 0 AND used_start_time IS NOT NULL,
                            (used_stop_time - used_start_time) , 0 ) 
      ) AS used,

   SUM(
        IF(invoice_stop_time > 0 AND invoice_start_time IS NOT NULL, 
                            (invoice_stop_time - invoice_start_time) , 0)   
       ) AS invoice

WHERE booked_stop_time > 0 
OR  used_stop_time > 0 
OR invoice_stop_time > 0

Upvotes: 0

Parixit
Parixit

Reputation: 3855

You can do it like this:

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

It return 0 because when your IF condition not satisfy then it set to 0 as final value, so just wrap the IF with SUM.

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

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.

Upvotes: 2

Related Questions