Sqlite retrieve data group by AptNo and produce averages for 7 and 14 days

Question

I am using SQLite. The system runs in an apartment complex and I wish to do a couple of counts grouped by the apartment numbers, one for 7 day average and one for 14 day average.

My test data is old, that's the reason I am going back to October, the following query gets me the 7 day average, but how do I also get 14 days in the same lines?

SELECT TimeStamp, CategoryOfPerson, AptNo, FirstName, LastName, count(*) / 7 as Ave_Over_7_Days from LocalDB
 where TimeStamp between date('now','-108 days') and date('now','-101 days')  
GROUP BY AptNo;

Results:

Timestamp           CategoryOfPerson AptNo  FirstName LastName Ave_over-7_Days
2020-10-05 14:01:41 TestData        101     101 Unknown 24
2020-10-05 16:07:46 TestData        102     102 Unknown 12
2020-10-05 14:01:15 TestData        103     103 Unknown 32
2020-10-05 14:14:20 TestData        104     104 Unknown 12
2020-10-05 14:02:01 TestData        105     105 Unknown 27
2020-10-05 14:14:40 TestData        106     106 Unknown 18
2020-10-05 14:03:58 TestData        107     107 Unknown 11
2020-10-05 14:17:48 TestData        108     108 Unknown 8
2020-10-05 16:05:21 TestData        109     109 Unknown 6
2020-10-05 14:19:08 TestData        110     110 Unknown 6
2020-10-05 14:04:21 TestData        111     111 Unknown 21
2020-10-05 14:14:00 TestData        115     115 Unknown 2

Edit: I also do not have to use between date now and - 108 days, its the only way I could think of to get what I wanted, there is possibly a better way, and seems to be restricting to to one average. Also thanks to the person who reformated my results below.

Raw Sample data set:

TimeStamp, CategoryOfPerson, AptNo, FirstName, LastName. My FirstName field contains Apt number

2020-10-01 18:59:34 TestData    101 101 Unknown
2020-10-01 22:11:38 TestData    102 102 Unknown
2020-10-05 14:01:15 TestData    103 103 Unknown
2020-10-05 14:01:41 TestData    101 101 Unknown
2020-10-05 14:02:01 TestData    105 105 Unknown
2020-10-05 14:03:58 TestData    107 107 Unknown
2020-10-05 14:04:21 TestData    111 111 Unknown
2020-10-05 14:13:38 TestData    103 103 Unknown
2020-10-05 14:14:00 TestData    115 115 Unknown
2020-10-05 14:14:20 TestData    104 104 Unknown
2020-10-05 14:14:40 TestData    106 106 Unknown
2020-10-05 14:14:59 TestData    101 101 Unknown
2020-10-05 14:15:45 TestData    103 103 Unknown
2020-10-05 14:16:09 TestData    105 105 Unknown
2020-10-05 14:16:29 TestData    103 103 Unknown
2020-10-05 14:16:53 TestData    111 111 Unknown
2020-10-05 14:17:17 TestData    105 105 Unknown
2020-10-05 14:17:48 TestData    108 108 Unknown
2020-10-05 14:18:20 TestData    107 107 Unknown
2020-10-05 14:18:42 TestData    103 103 Unknown
2020-10-05 14:19:08 TestData    110 110 Unknown
2020-10-05 15:58:12 TestData    106 106 Unknown
2020-10-05 16:04:12 TestData    107 107 Unknown
2020-10-05 16:04:29 TestData    106 106 Unknown
2020-10-05 16:05:11 TestData    108 108 Unknown
2020-10-05 16:05:21 TestData    109 109 Unknown
2020-10-05 16:05:35 TestData    105 105 Unknown
2020-10-05 16:06:13 TestData    104 104 Unknown
2020-10-05 16:06:46 TestData    103 103 Unknown
2020-10-05 16:07:46 TestData    102 102 Unknown
2020-10-05 16:08:05 TestData    101 101 Unknown
2020-10-05 16:08:23 TestData    111 111 Unknown
2020-10-05 16:08:43 TestData    110 110 Unknown
2020-10-05 16:09:02 TestData    106 106 Unknown
2020-10-05 16:09:25 TestData    107 107 Unknown
2020-10-05 16:09:41 TestData    106 106 Unknown
2020-10-05 16:10:00 TestData    108 108 Unknown
2020-10-05 16:10:19 TestData    109 109 Unknown
2020-10-05 16:10:36 TestData    105 105 Unknown
2020-10-05 16:10:53 TestData    104 104 Unknown
2020-10-05 16:11:10 TestData    103 103 Unknown
2020-10-05 16:11:27 TestData    102 102 Unknown
2020-10-05 16:11:46 TestData    101 101 Unknown
2020-10-05 16:12:09 TestData    111 111 Unknown
2020-10-05 16:15:12 TestData    101 101 Unknown
2020-10-05 16:15:38 TestData    105 105 Unknown
2020-10-05 16:15:57 TestData    101 101 Unknown
2020-10-05 16:16:25 TestData    103 103 Unknown
2020-10-05 16:16:50 TestData    101 101 Unknown
2020-10-05 16:17:27 TestData    104 104 Unknown
2020-10-05 16:17:38 TestData    111 111 Unknown```

Schema:

 CREATE TABLE "LocalDB" (
    "TimeStamp" NUMERIC,
    "CategoryOfPerson"  TEXT,
    "AptNo" TEXT NOT NULL,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL
);

question from:https://stackoverflow.com/questions/65893471/sqlite-retrieve-data-group-by-aptno-and-produce-averages-for-7-and-14-days

Answer 1

Im not quite sure this is the proper way to perform the task you are trying to accomplish, but I think it may work for you. Using a UNION statement in your Sqlite query would combine two SQL statements. I think, after playing around with your test data and DB schema, you would definitely need to run two queries. One Query with the where date range of 7 days and the second with the date range of 14 days, give this a try:

SELECT '7 Day Average', CategoryOfPerson, AptNo, FirstName, LastName, count(*) / 7 as Average from LocalDB 
 WHERE TimeStamp between date('now') and date('now','7 days') 
 GROUP BY AptNo 
UNION SELECT '14 Day Average', CategoryOfPerson, AptNo, FirstName, LastName, count(*) / 14 as Average from LocalDB 
 WHERE TimeStamp between date('now') and date('now','14 days') 
 GROUP BY AptNo;

You will have to adjust the two where statements to select data within your range, one for 7 days worth of data and the other for 14 days worth of data. This will then product a count by AptNo, divided by 7 and 14 respectively, showing the average number of accesses per unit # for that range.

You will wind up with two entries per grouped AptNo, but I think this would be the only way to get it done in one query.

Updated / Additional Answer:

So, I have crafted a query that will give you a single line response with each average here:

select distinct AptNo, CategoryOfPerson, FirstName, LastName, 
 (select (count(*) / 7) as '7_day_avg' from LocalDB where AptNo = this.AptNo and TimeStamp between date('now') and date('now','7 days')), 
 (select (count(*) / 14) as ’14_day_avg’ from LocalDB where AptNo = this.AptNo and TimeStamp between date('now') and date('now','14 days')) 
from LocalDB as this;

BUT The problem with this is that the CategoryOfPerson, FirstName, LastName will probably not be correct as the query is seeking the DISTINCT AptNo and the Category/Name associated with the first record it finds. So you could remove the name and category and just have a listing by AptNo with the averages, like this:

select distinct AptNo, 
 (select (count(*) / 7) as '7_day_avg' from LocalDB where AptNo = this.AptNo and TimeStamp between date('now') and date('now','7 days')), 
 (select (count(*) / 14) as ’14_day_avg’ from LocalDB where AptNo = this.AptNo and TimeStamp between date('now') and date('now','14 days')) 
from LocalDB as this;