Query to find a weekly average

Question

I have an SQLite database with the following fields for example:

date (yyyymmdd fomrat)
total (0.00 format)

There is typically 2 months of records in the database. Does anyone know a SQL query to find a weekly average?

I could easily just execute:

SELECT COUNT(1) as total_records, SUM(total) as total FROM stats_adsense

Then just divide total by 7 but unless there is exactly x days that are divisible by 7 in the db I don't think it will be very accurate, especially if there is less than 7 days of records.

To get a daily summary it's obviously just total / total_records.

Can anyone help me out with this?

Answer 1

Your weekly average is

daily * 7

Obviously this doesn't take in to account specific weeks, but you can get that by narrowing the result set in a date range.

Answer 2

I'm not sure how the syntax would work in SQLite, but one way would be to parse out the date parts of each [date] field, and then specifying which WEEK and DAY boundaries in your WHERE clause and then GROUP by the week. This will give you a true average regardless of whether there are rows or not.

Something like this (using T-SQL):

SELECT  DATEPART(w, theDate), Avg(theAmount) as Average
FROM    Table
GROUP BY DATEPART(w, theDate)

This will return a row for every week. You could filter it in your WHERE clause to restrict it to a given date range.

Hope this helps.

Answer 3

You could try something like this:

SELECT strftime('%W', thedate) theweek, avg(total) theaverage 

FROM table GROUP BY strftime('%W', thedate)

Answer 4

You'll have to omit those records in the addition which don't belong to a full week. So, prior to summing up, you'll have to find the min and max of the dates, manipulate them such that they form "whole" weeks, and then run your original query with a WHERE that limits the date values according to the new range. Maybe you can even put all this into one query. I'll leave that up to you. ;-)

Those values which are "truncated" are not used then, obviously. If there's not enough values for a week at all, there's no result at all. But there's no solution to that, apparently.

Answer 5

You can create your desired output using the weekly logic from this article:

http://blog.sqlauthority.com/2008/02/20/sql-server-udf-to-return-a-calendar-for-any-date-for-any-year/