CODEWITHSUNDEEP
sqlruby-on-railsrails-activerecord
alfitra
alfitra

Reputation: 27

Sum returns null in Rails

I am trying to subtract two value while querying the data from the database for creating a graph. but it always return null when i try the code

downtime = Ticket.by_month.where(:sla => true).sum("restore_time - start_time")

Upvotes: 0

Views: 731

Answers (2)

mu is too short
mu is too short

Reputation: 434665

You have null values in restore_time or start_time. In SQL, null - X and X - null are null for all X as are null + X and X + null. That means that a null value in any of your restore_time or start_time columns will turn the entire sum into null.

You could ignore the rows with nulls:

Ticket.by_month
      .where(:sla => true)
      .where.not(:restore_time => nil)
      .where.not(:start_time => nil)
      .sum('restore_time - start_time')

or convert them to something that makes sense in your context:

Ticket.by_month
      .where(:sla => true)
      .sum('coalesce(restore_time, something_sensible) - coalesce(start_time, something_sensible)')

or perhaps:

Ticket.by_month
      .where(:sla => true)
      .sum('coalesce(restore_time - start_time, something_sensible)')

Where something_sensible would be what you want to use in place of nulls, this value would of course have to have the same type as the first argument to the coalesce calls.

Upvotes: 3

Zulhilmi Zainudin
Zulhilmi Zainudin

Reputation: 9345

How about this?

restore_time = Ticket.by_month.where(:sla => true).sum("restore_time")
start_time = Ticket.by_month.where(:sla => true).sum("start_time")
downtime = restore_time - start_time

Upvotes: -1

Related Questions