how to use Group by specific prop in SQL query?

Question

I'm trying to show count of users and what type of users they are based on their age in the ranges provided, their gender, and the average trip duration. This is my current SQL: I receive a syntax error on the highlighted from, but why?

select count(distinct(usertype)), gender, avg(tripduration),
extract(year from current_date) - birth_year as age,
from(select age,
    case 
      when age <25 then "Under 25"
     when age between 26 and 35 then "26 to 35"
     when age between 36 and 45 then "36 to 45"
      when age between 46 and 55 then "46 to 55"
     when age > 55 then "Over 55"
    else "Invalad Birthdate"
from `project-1-349215.Dataset.tripdata` -- this from
 end as agerange)
`project-1-349215.Dataset.tripdata`
where bikeid is not null
group by usertype, gender, age

Does this answer your question? [How to group by month from Date field using sql](https://stackoverflow.com/questions/14565788/how-to-group-by-month-from-date-field-using-sql) — Vahid, Aug 01 '22 at 19:20
`CASE` statement should be ended with `END`. You have missed it up right before `FROM` clause — Alexey, Aug 02 '22 at 11:52

score 0 · Answer 1 · answered Aug 01 '22 at 21:08

You get the syntax error because your "from" is still inside your "case clause" in your subquery.

select age,
    case 
      when age <25 then "Under 25"
     when age between 26 and 35 then "26 to 35"
     when age between 36 and 45 then "36 to 45"
      when age between 46 and 55 then "46 to 55"
     when age > 55 then "Over 55"
    else "Invalad Birthdate"
from `project-1-349215.Dataset.tripdata` -- this should be after placed after "end as"
 end as agerange

Move your "from" after the end as agerage and the syntax error should be solved. Your subquery should look like this:

select age,
    case 
      when age <25 then "Under 25"
     when age between 26 and 35 then "26 to 35"
     when age between 36 and 45 then "36 to 45"
      when age between 46 and 55 then "46 to 55"
     when age > 55 then "Over 55"
    else "Invalad Birthdate"
    end as agerange
from `project-1-349215.Dataset.tripdata`

See examples of CASE usage in this document.

how to use Group by specific prop in SQL query?

1 Answers1