CODEWITHSUNDEEP
sqlsyntax-errorwith-clause
Moch
Moch

Reputation: 1

I am trying to run this query but it keeps giving me a syntax error for the GROUP BY statement. Can anyone please tell me what I might be doing wrong?

It keeps giving me a syntax error for the GROUP BY statement. I am trying to learn the WITH statement.

I am trying to run this query but it keeps giving me a message about DESC at the end of the GROUP BY function. Please tell me how can I correct this. I want the query to run and give me the duration of the longest bike ride. Below is the code I typed

WITH longest_used_bike AS (
  SELECT
   bike_id,
   SUM(duration_minutes) AS trip_duration
  FROM 
   bigquery-public-data.austin_bikeshare.bikeshare_trips
  GROUP BY 
   trip_duration DESC
  LIMIT 1
  )

And whenever I run it gives me this error:

Syntax error: Expected ")" but got keyword DESC at [8:18]

Upvotes: 0

Views: 167

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

I presume that what you have shown is just a snippet from your full query, since WITH needs to be followed by a SELECT. Also please as a matter of course tag the database you are using.

You need to break this up into two stages, first you need to find the durations for all bikes, and then you can take the highest. Something like this:

WITH bike_durations AS (
  SELECT
   bike_id,
   SUM(duration_minutes) AS trip_duration
  FROM 
   bigquery-public-data.austin_bikeshare.bikeshare_trips
  GROUP BY bike_id
  ),
longest_used_bike AS (
    SELECT bike_id FROM bike_durations
    ORDER By trip_duration DESC
    LIMIT 1)

Upvotes: 0

Related Questions