Combining two queries with case statement in BigQuery

I’m new to SQL and have been trying to combine two queries that give me a count of unique uses by day of the week (‘weekday’ – with days of the week coded 1-7) and by user type (‘member_casual’ – member or casual user). I managed to use a case statement to combine them into one table, with the following query:

SELECT weekday, 
  CASE WHEN member_casual = 'member' THEN COUNT (*) END AS member_total,
  CASE WHEN member_casual = 'casual' THEN COUNT (*) END AS casual_total,
FROM
  `case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`
GROUP BY  weekday, member_casual;

Resulting in a table that looks like this:

Row	weekday	member_total	casual_total
1	1	null	330529
2	1	308760	null
3	2	null	188687
4	2	316228	null
5	3	330656	null
6	3	null	174799

etc…

I can see that this likely has to do with the fact that I grouped by ‘weekday’ and ‘member_casual’, however I get errors if remove ‘member casual’ from the GROUP BY statement. I have tried to play around with a CASE IF statement instead, but have yet to find a solution.

Answer

You want countif():

SELECT weekday, 
       COUNTIF(member_casual = 'member') AS member_total,
       COUNTIF(member_casual = 'casual') AS casual_total,
FROM`case-study-319921.2020_2021_Trip_Data.2020_2021_Rides_Merged`
GROUP BY weekday;