create table earnings (tdate text not null, amount real not null);
create table expenses (tdate text not null, amount real not null);
insert into earnings values ('2010-01-01', 4000.00), ('2010-01-15', 4000.00);
insert into expenses values ('2010-01-02', 2500.00), ('2010-01-05', 2.00),
('2010-01-13', .2500);
with periods (sdate, edate)
as (select tdate,
(select date(min(tdate), '-1 day')
from earnings I
where I.tdate > O.tdate)
from earnings O),
totals (sdate, edate, earnings, expenses, days, cnt, avgday, avgcnt)
as (select sdate,
edate,
(select sum(amount)
from earnings
where tdate between sdate and edate),
(select sum(amount)
from expenses
where tdate between sdate and edate),
julianday(edate) - julianday(sdate) + 1,
(select count(*)
from expenses
where tdate between sdate and edate),
(select sum(amount)
from expenses
where tdate between sdate and edate) / (julianday(edate)
- julianday(sdate) + 1),
(select avg(amount)
from expenses
where tdate between sdate and edate)
from periods)
select * from totals where edate is not null;
2010-01-01|2010-01-14|4000.0|2502.25|14.0|3|178.732142857143|834.083333333333
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Keith Medcalf
>Sent: Saturday, 28 December, 2019 23:53
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] AVG Function HowTo
>
>
>The description makes no sense.
>
>So lets say on the 1st of a Month you have an "Earnings" of $4,000.
>On the 2nd of the month you have an "expense" of $2,500.
>On the 5th of the month you have an "expense" of $2.00.
>On the 15th of the month you have an "Earnings" of $4000.
>
>So the "average" expenses between the 1st and the 14 of the month is
>%1,251.
>
>If you had another expense of $0.25 on the 13th of the month the
>"average" would now be $834.083333333333333
>
>This makes no sense whatsoever as the "average" is a meaningless number.
>Do you perhaps want an "average expense per day" or something that might
>actually have meaning?
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>>Behalf Of Csanyi Pal
>>Sent: Saturday, 28 December, 2019 05:07
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: [sqlite] AVG Function HowTo
>>
>>Hi,
>>
>>I have attached here the exported sql file of my little sqlite database.
>>
>>I am a newbe in the sqlite language so I ask here only an advice in
>>which direction to go, eg. which functions of sqlite language to use to
>>achieve my goal?
>>
>>My issue is the following.
>>
>>So far my database has two tables only: the Earnings, and the Expenses
>>table only.
>>
>>I have two goals here:
>>
>>1. goal is to calculate the Average of Expenses BETWEEN Dates From I.
>>Earnings To Date minus 1 Day before II. Earnings in a Month;
>>
>>2. goal is to calculate the Average of Expenses BETWEEN Dates From II.
>>Earnings in a Month To Date minus 1 Day before I. Earnings in The Next
>>Month.
>>
>>I am thinking like this regarding my 1. goal:
>>---------------------------------------------
>>
>> The task of this View is described below.
>> Find the expenses entered that are between the following two dates:
>> within one month from the date of the I. earnings to the
>> day before the day of the II. earnings.
>> That is, based on the data entered,
>> 1. Step: in this step find from records
>> starting on the I. earnings date of the first month
>> up to the day before the second earnings date of the same month,
>then
>> sum all these expenses, and get the first partial result, which I
>>call here E1.
>> Then continue with the same
>> in the second step, the next one, ie with the
>> 2. Step: starting on the I. earnings date of the second month
>> up to the day before the second earnings date of the same month,
>then
>> gets the partial result E2.
>> 3., 4., n. Steps..
>> Then continue with the same steps until it arrives
>> until this month - if there exists the II. Earnings in the Month, or
>> until the Month before this Month ( in which there is surtenly the
>>II. Earnings),
>> and calculate the En result for it.
>> When it have completed these steps, calculate the average of the
>>partial results E1, E2, ..., En.
>> Do all this with a single View.
>>
>>I am thinking like this regarding my 2. goal:
>>---------------------------------------------
>> The task of this View is described below.
>> Find the expenses entered that are between the following two dates:
>> within one month from the date of the II. earnings to the
>> day before the day of the I. earnings in the Next Month.
>> That is, based on the data entered,
>> 1. Step: in this step find from records
>> starting on the II. earnings date of the first month
>> up to the day before the first earnings date of the following (
>>second ) month, then
>> sum all the expenses, and get the first partial result, which I call
>>here E1.
>> Then continue with the same
>> in the second step the next one, like:
>> 2. Step: starting on the II. earnings date of the second month
>> up to the day before the first earnings date of the following (
>>third ) month
>> and gets the partial result E2.
>> 3., 4., n. Steps..
>> Then continue with the same steps until it arrives
>> until the month before this month, and calculate the En result for
>>it.
>> When it have completed these steps, calculate the average of the
>>partial results E1, E2, ..., En.
>> Do all this with a single View.
>>
>>Do I think well?
>>
>>Any advices will be appreciated!
>>
>>--
>>Best, Pali
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
