January 15, 2019 at 3:00 pm
I have a massive data set which breaks down how many trips were taken on which days of which months for years 2016 & 2017. I found out how many trips were taken per month of each given year but now it is asking me to
find out the average trips per day of each given month... how would I query this? To get the number of trips per month I used
SELECT MONTH(start_date), COUNT(*) AS Count
FROM trips
WHERE YEAR(start_date) = 2016
GROUP BY MONTH(start_date);
January 15, 2019 at 3:20 pm
If you have a Calendar table, this is infinitely easier. You would outer join Calendar to your Trips table and then aggregate on columns in the Calendar table (Year, Month, DayOfWeek) etc.
January 15, 2019 at 3:27 pm
You'll also need to use a CTE or derived table, because you have aggregates on two different scales. You'll first need to count the number of trips per day, and then average those counts over the month.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 16, 2019 at 4:30 am
paige.miller91 - Tuesday, January 15, 2019 3:00 PMI have a massive data set which breaks down how many trips were taken on which days of which months for years 2016 & 2017. I found out how many trips were taken per month of each given year but now it is asking me to
find out the average trips per day of each given month... how would I query this? To get the number of trips per month I usedSELECT MONTH(start_date), COUNT(*) AS Count
FROM trips
WHERE YEAR(start_date) = 2016
GROUP BY MONTH(start_date);
You need to divide the count by the number of days in that month:
SELECT MONTH(start_date) Month,
DAY(EOMONTH(start_date)) DaysInMonth,
COUNT(*) AS TripsInMonth,
CONVERT(decimal(9,1),COUNT(*)/(DAY(EOMONTH(start_date))+0.0)) AverageTripsPerDay
FROM trips
WHERE YEAR(start_date) = 2016
GROUP BY MONTH(start_date),DAY(EOMONTH(start_date));
January 16, 2019 at 7:38 am
Jonathan AC -- it keeps giving me a syntax error
January 16, 2019 at 7:43 am
paige.miller91 - Wednesday, January 16, 2019 7:38 AMJonathan AC -- it keeps giving me a syntax error
What's the error you get? Where does the cursor go to if you double click the red error text?
January 16, 2019 at 7:47 am
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decimal(9,1),COUNT(*)/(DAY(EOMONTH(start_date))+0.0)) AverageTripsPerDay
FROM t' at line 4 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */
January 16, 2019 at 8:05 am
This is a MSSQL forum. Convert it a MSSQL function. Try cast:
SELECT MONTH(start_date) Month,
DAY(EOMONTH(start_date)) DaysInMonth,
COUNT(*) AS TripsInMonth,
cast(COUNT(*)/(DAY(EOMONTH(start_date))+0.0) as decimal(9,2)) AverageTripsPerDay
FROM trips
WHERE YEAR(start_date) = 2016
GROUP BY MONTH(start_date),DAY(EOMONTH(start_date));
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 16, 2019 at 8:09 am
paige.miller91 - Wednesday, January 16, 2019 7:47 AMSQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decimal(9,1),COUNT(*)/(DAY(EOMONTH(start_date))+0.0)) AverageTripsPerDay
FROM t' at line 4 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */
MySQL doesn't have an EOMONTH function. I'm not an expert in MySQL but I think it has a LAST_DAY() function which is the same functionality.
January 16, 2019 at 9:13 am
The Last_day function worked! And yeah my bad, I had to use CAST as well. Thank you guys so much, it worked!
January 16, 2019 at 1:06 pm
Sorry for all the questions, this should be the last one: I have the date in the DATETIME format and the question asked me to find the average trip length for the tnire data set,
So first I isolated the time from the trip start_date and end_date into 2 columns by using:
SELECT CAST(start_date as TIME) time1, CAST(end_date as TIME) time2
FROM trips
Now to average the trip length across the entire set I would have to do AVERAGE of (time2 - time1) ; how do I do this using the query above, how would I subquery if need be?
January 16, 2019 at 3:34 pm
paige.miller91 - Wednesday, January 16, 2019 1:06 PMSorry for all the questions, this should be the last one: I have the date in the DATETIME format and the question asked me to find the average trip length for the tnire data set,
So first I isolated the time from the trip start_date and end_date into 2 columns by using:SELECT CAST(start_date as TIME) time1, CAST(end_date as TIME) time2
FROM tripsNow to average the trip length across the entire set I would have to do AVERAGE of (time2 - time1) ; how do I do this using the query above, how would I subquery if need be?
The average trip length (if it's average time taken we would usually call this duration) can be calculated from the SUM of the duration of each trip divided by the count of trips. Or with using the AVG function to find the average trip length.
So you need to be able to calculate the trip length, can take the average of the datediff of the two date time columns, in t-sql:SELECT AVG(DATEDIFF(ss,start_date, end_date)) as AverageTripDurationSecs
FROM trips
In MySQL you could write it like this:SELECT AVG(TIME_TO_SEC(TIMEDIFF(start_date, end_date)) AverageTripDurationSecs
FROM trips
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply