Treating Nil as Zero in Sum Function

Treating nil as zero in sum function

One way is:

items.to_a.sum { |e| e.sale_price.to_i } # or to_f, whatever you are using

Methods like #to_f and #to_i will turn nil into 0.

How might I treat nil values as zero when adding numbers?

As @jforberg points out, you can just use the #to_i method which will return 0 for nil.

def calculate_the_thing(hsh)
  hsh[:x].to_i + hsh[:y].to_i + hsh[:z].to_i
end

Alternatively, you can also define the hash with an automatic default value...

hsh = Hash.new{0}

But if you have a method that explicitly puts nil as a hash value that will override the default value.

Rails: sum should retun nil if given column values is null

SUM is ignoring null values, so columns with NULL values will always be 0 as zero + nothing is 0

To overcome this I have used condition like:

Price.where("cost IS NOT NULL).group(:name).sum(:cost)

This request will get non null cost values and sum them. After that, I can fill with NULL the cost columns of the other records.

This way I can make sure that if cost is actually 0.0 then I get sum(cost) as 0.0 instead of NULL.

sql SUM: change null with 0

In general you would first set NULL values to 0 and then SUM them:

SELECT SUM(COALESCE(bgrealisasi0.n_nilai, 0)) ...

Otherwise your queries have a few deficiencies such as field LIKE '%' which is a really inefficient way of writing field IS NOT NULL. The use of a scalar sub-query makes the overall query really difficult to read and the joins can probably be optimized if rewritten as a sub-query instead.

How do I get SUM function in MySQL to return '0' if no values are found?

Use COALESCE to avoid that outcome.

SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...

To see it in action, please see this sql fiddle: http://www.sqlfiddle.com/#!2/d1542/3/0

---

More Information:

Given three tables (one with all numbers, one with all nulls, and one with a mixture):

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE foo
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO foo (val) VALUES
(null),(1),(null),(2),(null),(3),(null),(4),(null),(5),(null),(6),(null);

CREATE TABLE bar
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO bar (val) VALUES
(1),(2),(3),(4),(5),(6);

CREATE TABLE baz
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO baz (val) VALUES
(null),(null),(null),(null),(null),(null);

Query 1:

SELECT  'foo'                   as table_name,
        'mixed null/non-null'   as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    foo
UNION ALL

SELECT  'bar'                   as table_name,
        'all non-null'          as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    bar
UNION ALL

SELECT  'baz'                   as table_name,
        'all null'              as description,
        0                       as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    baz

Results:

| TABLE_NAME |         DESCRIPTION | EXPECTED_SUM | ACTUAL_SUM |
|------------|---------------------|--------------|------------|
|        foo | mixed null/non-null |           21 |         21 |
|        bar |        all non-null |           21 |         21 |
|        baz |            all null |            0 |          0 |

Sum of items in Rails model - Nil class error

One or more of your associations are likely nil — the optional: true key indicates that this might be happening.

You can either check to see that these associations are present? in your block, or you can assign a total to line_items which will do this work for you. I'd propose the latter:

class LineItem < ApplicationRecord
  def total_as_product
    return 0 unless product.present? 
    product.price.to_i * self.quantity
  end 

  def total_as_service
    return 0 unless service.present?
    service.price.to_i * self.quantity
  end 
end 

And in your Order model:

def total_product_items
  self.line_items.collect { |item| item.total_as_product }.sum
end 

def total_service_items
  self.line_items.collect { |item| item.total_as_service }.sum
end 

Something to keep in mind as you're going along: there are some further optimizations that could be made here — such as eager-loading your associations so you don't get hit with some n+1 queries.

Also: before_update is redundant when before_save is present. You can safely remove before_update and get the same behavior.

Changing a SUM returned NULL to zero

Put it outside:

SELECT COALESCE(

(  
    SELECT SUM(i.Logged)  
    FROM tbl_Sites s  
    INNER JOIN tbl_Incidents i  
    ON s.Location = i.Location  
    WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
    GROUP BY s.Sites  
), 0)  AS LoggedIncidents

If you are returning multiple rows, change INNER JOIN to LEFT JOIN

SELECT COALESCE(SUM(i.Logged),0)
FROM tbl_Sites s  
LEFT JOIN tbl_Incidents i  
ON s.Location = i.Location  
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
GROUP BY s.Sites  

By the way, don't put any function or expression inside aggregate functions if it's not warranted, e.g. don't put ISNULL, COALESCE inside of SUM, using function/expression inside aggregation cripples performance, the query will be executed with table scan

Return null in SUM if some values are null

You can replace nulls with NaNs using coalesce:

df2 = df.groupBy('product').agg(
    F.sum(
        F.coalesce(F.col('price'), F.lit(float('nan')))
    ).alias('sum(price)')
).orderBy('product')

df2.show()
+-------+----------+
|product|sum(price)|
+-------+----------+
|      A|     250.0|
|      B|     200.0|
|      C|       NaN|
+-------+----------+

If you want to keep integer type, you can convert NaNs back to nulls using nanvl:

df2 = df.groupBy('product').agg(
    F.nanvl(
        F.sum(
            F.coalesce(F.col('price'), F.lit(float('nan')))
        ),
        F.lit(None)
    ).cast('int').alias('sum(price)')
).orderBy('product')

df2.show()
+-------+----------+
|product|sum(price)|
+-------+----------+
|      A|       250|
|      B|       200|
|      C|      null|
+-------+----------+

---

Related Topics