Count multiple string and put the count of each string into column

I’m really a newbie in sql and bigquery. In this case I use bigquery-public-data.san_francisco_bikeshare.bikeshare_trips dataset (image 1 and 2).

I want to count total of subscirber_type, ‘Subscriber’ and ‘Customer’, and their average duration_sec.

So the column I want to make it consist of:

start_station name | total_Subscriber | total_nonSubscriber | avg_duration_Subscriber | avg_duration_nonSubscriber

I hope you guys understand my question and could give me a help. Thanks in advance.

Answer

you’ve not provided detail information for your output , but if I understand cirrectly , you want to do this:

select count(*) totalCount
      ,count(case when subscriber_type ='Customer' then 1 end) CustomerCount
      ,count(case when subscriber_type ='Subscriber' then 1 end) subscriberCount
      ,avg(case when subscriber_type ='Customer' then durationsec end) CustomerAvg
      ,avg(case when subscriber_type ='Subscriber' then durationsec end) SubscriberAvg
from tablename

but if you want count and avg per start station then you need to group by start_station:

select start_station
      ,count(*) totalCount
      ,count(case when subscriber_type ='Customer' then 1 end) CustomerCount
      ,count(case when subscriber_type ='Subscriber' then 1 end) subscriberCount
      ,avg(case when subscriber_type ='Customer' then durationsec end) CustomerAvg
      ,avg(case when subscriber_type ='Subscriber' then durationsec end) SubscriberAvg
from tablename
group by start_station