How to make the name of the table shorter and how to fix the first select that has no name table

Question

I have a very long name for a table "bigquery-public-data.new_york.nypd_mv_collisions" union all and also I don't know where to put the name for the first select.I'm trying to create one row for each vehicle type and count each of them. I want to call the long table name c or something like that, but what is the right order?

I get an error: Unrecognized name: vehicle_type_code1 at [1:8]

select vehicle_type_code1, vehicle_type_code2, vehicle_type_code_3, vehicle_type_code_4, 
vehicle_type_code_5,count(vehicle) as count_all
from  
  (
select vehicle_type_code1 as vehicle from bigquery-public-data.new_york.nypd_mv_collisions 
union all
select vehicle_type_code2 as vehicle from bigquery-public-data.new_york.nypd_mv_collisions 
union all 
select vehicle_type_code_3 as vehicle from bigquery-public-data.new_york.nypd_mv_collisions 
union all 
select vehicle_type_code_4 as vehicle from bigquery-public-data.new_york.nypd_mv_collisions 
union all 
select vehicle_type_code_5 as vehicle from bigquery-public-data.new_york.nypd_mv_collisions 
  )c
Group by vehicle
order by dasc
limit 3;

how to fix it?

Thank you

Tag your question with the database you are using (which is presumably BigQuery). — Gordon Linoff, Aug 05 '21 at 11:14

score 0 · Answer 1 · answered Aug 05 '21 at 11:16

If you want to count the vehicles in the columns using BigQuery, you can unpivot using arrays and unnest():

select vehicle_type_code, count(*) as count_all
from  `bigquery-public-data.new_york.nypd_mv_collisions` c cross join
      unnest(array[vehicle_type_code1, vehicle_type_code2, vehicle_type_code3, vehicle_type_code4, vehicle_type_code5]
            ) vehicle_type_code
group by 1;

How to make the name of the table shorter and how to fix the first select that has no name table

1 Answers1