Home > Software engineering >  How can eliminate the line that counts everything [COUNT function in SQL]
How can eliminate the line that counts everything [COUNT function in SQL]

Time:08-01

I was running a code where I needed to see the top 10 results in a table. Unfortunately, I am with one more row that I was expecting.

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

My results was the following: [Result after the query][1] [1]: https://i.stack.imgur.com/KCu7W.png

Is there any way I can eliminate row 1 from my query?

Thank you in advance for any assistance!

CodePudding user response:

try this:

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 start_station_name is not null
 GROUP BY
  start_station_name, end_station_name, usertype
 ORDER BY
  num_trips DESC
 LIMIT 10
  • Related