CODEWITHSUNDEEP
google-bigquery
yawwml
yawwml

Reputation: 87

A simple SUM on all records of a column doesn't work in Big Query

Hi I am trying to find total revenue earned by cabbies from this data set on google big query here but sum(total_amount) doesn't seem to work. It says

Error: Field total_amount is of type STRING which is not supported for SUM

even though it is defined as numeric data type by default. I did some casting things but then the sum says 0.

I don't seem to find why.

Things I tried:

  1. Specifying the column as INTEGER in SUM like SUM(INTEGER(total_amount)) returned 0.
  2. CASTED the column name to INTEGER using CAST type returned 0.
  3. Checked if there are any NULL columns but there is none.

Here is my query:

Select sum(total_amount) from
[bigquery-public-data:new_york_taxi_trips.tlc_yellow_trips_2018]

What am I missing here?

Upvotes: 1

Views: 1927

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

NUMERIC data type in Legacy SQL has limited support

So, instead try running your query in BigQuery Standard SQL as in below example 

#standardSQL 
SELECT SUM(total_amount) 
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018`

with result 

Row f0_  
1   1837861124.95

Upvotes: 2

Related Questions