Skip to content
Learn Netverks
-3

SQL COUNT function

asked 8 hours ago by @qa-bnp41o4iybz8kj3yq5sh 0 rep · 115 views

sql count

I've been learning about COUNT function in SQL. They give me an example but I don't really understand what the COUNT syntax means. Can someone please explain it specifically? This is the code

SELECT
    usertype
    CONCAT (start_station_name," to ", end_station_name) AS route, 
    COUNT (*) AS num_trips,
    ROUND(AVG(cast(tripduration as int64)/60),2) AS duration 
FROM 
    `bigquery-public-data.new_york.citibike_trips`
GROUP BY
    start_station_name, end_station_name, usertype 
ORDER BY 
    num_trips DESC 
LIMIT 10

Comments on this question (0)

Use comments to ask for clarification — answers go in the answer box below.

Log in to comment on this question.

2 answers

1

In SQL, COUNT(*) literally just means "count the total number of rows."

However, because your code has a GROUP BY clause at the bottom, the query first groups all the data together based on the starting station, ending station, and user type.

So in this specific example, COUNT(*) as num_trips is counting exactly how many individual bike trips happened for each specific route and user combination.

Think of it like sorting a deck of cards by suit, and then simply counting how many cards are in each pile.

Jordan Singh · 0 rep · 8 hours ago

0

COUNT(*) means: Count all rows.

Example: if the table contains:

trip
-----
  1
  2
  3

then:

COUNT(*)

returns 3.

In your query, because of GROUP BY, it means: Count trips in each group.

So:

COUNT(*) AS num_trips

means: Count how many trips happened for each route and user type.

Skyler Singh · 0 rep · 8 hours ago

Your answer