SQL query: Extra line with no data-CodePudding

I´m trying to get the first 10 records when concatenating two strings, counting the number of records for these, and getting the average duration of the trips. The query jeeps showin the first line of the results with no data (maintaining the "to" in the route column, and null duration) what it affects the results... How can I get rid of that first zero line? (There are no empty fields in de database, so I do not know Why this line pops)

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

query output

CodePudding user response：

It looks that your FROM table has plenty of empty rows. And as there is no WHERE clause in your query they are also picked and counted.

Try that Query:

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` 
WHERE usertype != '' AND start_station_name != '' AND end_station_name != ''
GROUP BY start_station_name, end_station_name, usertype
ORDER BY num_trips DESC
LIMIT 10