Calculating average daily counts seems like something that would be done fairly often, and yet, I have never done it. I asked my wife, who is also a programmer of database-backed applications, and she never had the occasion to do so either! So, it is with great enthusiasm that I take on this challenge today.
To qualify what is meant by an "average daily count", for the purposes of this blog, it describes a monthly count of patients at a doctor's office, widgets manufactured, or products sold within a month. The daily average is then calculated by dividing the monthly count by the number of days in a month so that we know how much the daily count contributed towards the monthly total. For example, if a car dealership sold 10 Honda Civics in the month of May, then those 10 sales represent an average of 0.32 of one vehicle per day. Meanwhile, if the dealership were to sell a whopping 50 Honda Civics in one month, then the daily average would soar to 1.61 Honda Civics per day.
The Query
Our SELECT statement will query the Sakila Sample Database to tabulate movie rentals for each month in the following format:
ID| MONTH | MONTHLY_COUNT | AVG_DAILY_COUNT ------------------------------------------- 1| Jan | 152 | 10.3 2| Jan | 15000 | 1611 3| Jan | 14255 | 2177 1| Feb | 4300 | 113 2| Feb | 9700 | 782 3| Feb | 1900 | 97 etc...
The AVG_DAILY_COUNT column above increases the complexity of the query substantially because we need the obtain the monthly counts first. Therefore, the query consists of both inner and outer SELECT statements. Here is the inner query and results, sorted by year, month, and inventory_id:
The Outer Query
From that data, we can tabulate the average daily counts as follows:
I included the number of days in each month for reference, since it plays a key role in calculating the daily averages. It's also edifying to see how it's obtained, since the number of days in each month is required to calculate the average daily count. Here is that code isolated from the rest of the query:
datediff(day,
datefromparts(rental_year, rental_month, 1),
dateadd(month, 1, datefromparts(rental_year, rental_month, 1))) days_in_month
The datediff() function returns the number of days between the first day of the month to the first day of the following month. The datefromparts() function creates a date from the rental_year and rental_month columns of the inner query.
We can see the same code in the calculation of the daily_avg:
round(
cast(cnt as FLOAT) / cast(datediff(day,
datefromparts(rental_year, rental_month, 1),
dateadd(month, 1, datefromparts(rental_year, rental_month, 1))) as FLOAT
), 4
) daily_avg
Notice that both the Dividend (cnt) and divisor (days in month) have to be cast to a FLOAT. Otherwise, decimals are discarded in the calculation. We want to keep as much precision as possible until the end, where we round to four decimal places.
Conclusion
In today's blog, we calculated the average daily counts for a given column in SQL Server using Navicat for SQL Server. Interested in giving Navicat for SQL Server a try? You can download it for 14 days completely free of charge for evaluation purposes!
