Using COUNT returns the same value when counting two different columns

Question

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.

J.D. · Accepted Answer · 2023-03-02 13:05:08Z

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

Stack Exchange Network

Using COUNT returns the same value when counting two different columns

1 Answer 1

Your Answer

Hot Network Questions

Using COUNT returns the same value when counting two different columns

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions