CODEWITHSUNDEEP
sqlgoogle-bigquerylooker-studio
Danf
Danf

Reputation: 1569

Avoid 'infinity' in data aggregates in Google Data Studio or SQL

I am building a report in Data Studio, and the data comes from a BigQuery dataset.

The problem is that some values in the table are Infinity, and when Data Studio aggregates in totals or average, the result is always Infinity.

I need to avoid those values in those aggregates. I am trying to do that in Data Studio, or to replace those values by zero in the SQL query.

Is there a way to do either of those things? Is there another way to deal with this?

Thanks in advance.

Upvotes: 0

Views: 1027

Answers (2)

Rushil Kapoor
Rushil Kapoor

Reputation: 11

You can use the "is_inf" function in the where clause and remove any bad data records before aggregation

SELECT
   SUM(col2) as aggregated_value
FROM TABLE 1
WHERE IS_INF(col2) = false

Upvotes: 1

AssureTech
AssureTech

Reputation: 36

Using a Case Statement in your aggregation is likely where you'd like to go. If you want to replace this with 0 then the below logic will work.

SELECT
   SUM(
      CASE
         WHEN col2 = inf THEN 0
         ELSE col2
      END
    ) as aggregated_value
FROM TABLE 1;

If you need to do an average, then 0 isn't a good replacement. Instead use NULL as SQL will ignore NULLS in an average:

SELECT
   AVG(
      CASE
         WHEN col2 = inf THEN NULL
         ELSE col2
      END
    ) as aggregated_value
FROM TABLE 1;

Upvotes: 0

Related Questions