January 15, 2019 at 11:37 am
I have a massive data set that breaks down bicycle trips by yyyy-mm-dd 00:00:00 format, I am having to search for how many trips were done in the year 2016 broken down by month, the start_date column contains the info about when the trips started, currently I have
SELECT COUNT(*), start_date
FROM trips
WHERE YEAR(start_date) = '2016-10-26'
GROUP BY start_date;
This gives me the count for all trips broke down to ones done at the same timestamp, I need it grouped by month...not sure how to do it.
January 15, 2019 at 11:41 am
You need to calculate the month from the date. A date is just a number...
SELECT MONTH(start_date), COUNT(*) AS Count
FROM trips
WHERE YEAR(start_date) = 2016
GROUP BY MONTH(start_date);
January 15, 2019 at 11:46 am
Ahhh ok, thank you so much!
January 15, 2019 at 12:22 pm
If I had to calculate the average number of trips per day for each yeah-month combination, would I add to the above query or does it have to be separate ones for each month?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply