Count combination frequency-CodePudding

I have such an assignment. I believe my guess is correct, however I didn't find anything confirming my assumption how frequency works with count function.

What was the most popular bike route (start/end station combination) in DC’s bike-share program over the first 3 months of 2012? How many times was the route taken?

• duration seconds: duration of the ride (in seconds)

• start time, end time: datetimes representing the beginning and end of the ride

• start station, end station: name of the start and end stations for the ride

This is the code I wrote, wanted to see if my guess regarding most popular route (i believe it is a frequency) is correct with COUNT combination.

SELECT start_station, end_station, count(start_station || end_station) AS "# route taken"
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time BETWEEN '2012-01-01' AND '2012-03-31'
GROUP BY start_station, end_station
ORDER BY count(start_station || end_station) DESC

If someone can confirm if my guess is right, I will appreciate.

CodePudding user response：

SELECT start_station, end_station, count(*) AS ct_route_taken
FROM   tutorial.dc_bikeshare_q1_2012
GROUP  BY start_station, end_station
ORDER  BY ct_route_taken DESC
LIMIT  1;

Just count(*).

The name of the table would indicate that we need no WHERE clause.
If that's misleading and it covers a greater time interval, add a (proper!) WHERE clause like this:

WHERE  start_time >= '2012-01-01'
AND    start_time <  '2012-04-01'

Your query would eliminate most of '2012-03-31', since start_time is supposed to be a "datetime" type. Depending on which type exactly and where the date of "the first 3 months" is supposed to be located, we might need to adjust for time zone also.

See:

CodePudding user response：

From description and the query look like ok if the start station and end station description are same for each and every station. however without looking into the table data it is little difficult to confirm.