Using COUNT on SQL in GoogleSQL for two variables

Question

I am trying to do COUNT for two variables in rider_type and three variables in bike_type.

I am only able to get it to work like this:

SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'casual'
GROUP BY bike_type
UNION ALL
SELECT bike_type,
COUNT(rider_type) AS num_of_rides
FROM `cyclistic-395223.trips.12_month_tripdata_clean`
WHERE rider_type = 'member'
GROUP BY bike_type

Essentially I want the results to show the COUNT grouped by both bike_type AND rider_type. Something like this:

Database schema:

Answer 1

You can group by more than one field.

SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM cyclistic-395223.trips.12_month_tripdata_clean
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type

Example:

WITH _table AS (
    SELECT 'casual' AS rider_type, 'docked_bike' AS bike_type UNION ALL
    SELECT 'casual', 'classic_bike'UNION ALL
    SELECT 'casual', 'electric_bike' UNION ALL
    SELECT 'member', 'docked_bike' UNION ALL
    SELECT 'member', 'electric_bike' UNION ALL
    SELECT 'member', 'classic_bike' UNION ALL
    SELECT 'member', 'docked_bike'
  )
SELECT bike_type, rider_type, COUNT(*) AS num_of_rides
FROM _table
WHERE rider_type IN ('casual', 'member')
GROUP BY bike_type, rider_type

bike_type	rider_type	num_of_rides
docked_bike	casual	1
classic_bike	casual	1
electric_bike	casual	1
docked_bike	member	2
electric_bike	member	1
classic_bike	member	1