SQL in BigQuery data returning as null

Question

Question: When running a query in BigQuery, with the SQL command

SELECT 
 usertype,
 CONCAT(start_station_name,'to',end_station_name) as route,
 COUNT(*) as num_trips,
 Round(AVG(Cast(tripduration as int64)/60),2) as duration
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
 GROUP BY start_station_name, end_station_name, usertype
 ORDER BY num_trips DESC 
 LIMIT 10

I am getting the 1st row of data as empty and null and I do not understand why? Can someone please explain what I did wrong or why the data is not complete when returned. Is it due to times going into next day or before and afternoon?

usertype    route   num_trips   duration
    to  5828994 
Customer    Central Park S & 6 AvetoCentral Park S & 6 Ave  46671   50.36
Customer    Grand Army Plaza & Central Park StoGrand Army Plaza & Central Park S    21039   58.02
Customer    Centre St & Chambers SttoCentre St & Chambers St    17543   34.75
Subscriber  W 21 St & 6 Aveto9 Ave & W 22 St    17260   5.23
Subscriber  W 21 St & 6 AvetoW 22 St & 10 Ave   14715   6.94
Subscriber  Pershing Square NorthtoW 33 St & 7 Ave  12559   8.4
Customer    Broadway & W 60 SttoBroadway & W 60 St  12528   52.09
Subscriber  W 22 St & 10 AvetoW 22 St & 8 Ave   11764   3.58
Subscriber  Pershing Square NorthtoE 24 St & Park Ave S 11737   7.03

rtenha · Accepted Answer · 2021-10-14 17:05:10Z

1

You haven't done anything incorrectly in your SQL. This is just a case where the table has bad/blank/null data.

select count(*), sum(tripduration)
from `bigquery-public-data.new_york_citibike.citibike_trips`
where usertype = '' or start_station_name = '' or end_station_name = ''

If you want to clean up your original query, just add a where usertype <> ''.

answered Oct 14, 2021 at 17:05

rtenha

3,6281 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MatBailie Over a year ago

Won't find and highlight NULL entries. Needs OR x IS NULL or COALESCE(x, '') = ''

rtenha Over a year ago

@MatBailie agreed, but there actually aren't any NULLs in the fields I referenced, they are all '' empty strings.

MatBailie Over a year ago

This is just a case where the table has bad/blank/null data.

Collectives™ on Stack Overflow

SQL in BigQuery data returning as null

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related