1

I am new to SQL and am using it for a school project and would like some help.

I have a table with multiple columns about a fictional company that provides a bike sharing service. One of the rows I am trying to count is called member_casual which returns a string with either casual or member, that reports whether a user is a member or not. The second column I am trying to count is a string column called rideable_type that reports what kind of bike it has. I want to create a table that returns the number of kinds of bikes per each kind or rider, casual or member.

Here is my code:

SELECT member_casual,
  COUNT(member_casual) AS member_type,
  COUNT(rideable_type) AS electric_classic
FROM `coursera-practice-356200.cycle_data.cycle_table1` 
GROUP BY member_casual

Here is my result:

member_casual member_type electric_classic
casual 21416 21416
. member. . 94193. . 94193.

I know my code is flawed, but I do not know where it is flawed. I also do not know why I got the same number when I am counting two different columns (member_casual and rideable_type) which should be very different numbers.

I guess what I am asking is for assistance on where my code is wrong and how to fix it.

1 Answer 1

1

I also do not know why I got the same number when I am counting two different columns (member_casual and rideable_type) which should be very different numbers.

The COUNT() function counts the number of non-null values in the column. There are no null values in your member_casual and rideable_type columns, so they share the same COUNT(), which effectively is just the number of rows in the grouping on member_casual.

I want to create a table that returns the the number of kind of bikes per each kind or rider, casual or member.

You need to group on both of those fields then, to be able to get the counts of values in each within each other, like so:

SELECT
    member_casual,
    rideable_type,
    COUNT(rideable_type) AS bike_type_counts
FROM coursera-practice-356200.cycle_data.cycle_table1 
GROUP BY member_casual, rideable_type

Or to ypercube's point, if you what you're looking for is to count the unique number of rideable_types in each member_casual, then you just need to add the DISTINCT keyword to the COUNT() function in your original query like so:

SELECT
    member_casual,
    COUNT(member_casual) AS member_type,
    COUNT(DISTINCT rideable_type) AS electric_classic
FROM coursera-practice-356200.cycle_data.cycle_table1 
GROUP BY member_casual
3
  • Maybe they just need to use DISTINCT in the count, eg COUNT(DISTINCT rideable_type) Mar 2, 2023 at 12:25
  • @ypercubeᵀᴹ Yes good point, updated. Depends on what OP's goal is. But you probably interpreted better.
    – J.D.
    Mar 2, 2023 at 12:48
  • 1
    No, either option maybe what they want. It's not clear. Mar 2, 2023 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.