Re: [sqlite] AVG Function HowTo
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.0833
--
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 On
>Behalf Of Keith Medcalf
>Sent: Saturday, 28 December, 2019 23:53
>To: SQLite mailing list
>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.083
>
>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 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
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.083 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 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
Re: [sqlite] AVG Function HowTo
SQL is a declarative language, not a procedural one. Therefore I find it helps to think in declarative terms rather than procedural. What that means practically in this case is don’t think in terms of loops. Think about what you want, and think about how your data is related. I don’t entirely understand what you want. Am I correct in restating it as: ‘You want to know the expenses in each earning period.‘ Expenses and earnings are related by date. So, I suggest: - you want to group your expenses data by the last earning date. - you want to run an aggregate function (likely SUM, but I’m not entirely sure what you’re trying to do) within each group. - SQL supports comparison operators on dates (assuming you store them in an appropriate format, like integer timestamp or ISO86somethingsomething). Something like the following might give you what you want: SELECT SUM(expense) as period_expenses, (SELECT MAX(ear.Date) FROM earnings AS ear WHERE ear.Date <= exp.Date) AS last_earning_date FROM Expenses AS exp GROUP BY last_earning_date This might not be what you want but is perhaps a starting point of how things are done in SQL. You can also achieve that without the sub select by using joins and window functions or a variety of other ways, the above is just how I think about it. Also you might want to use date functions to strip out only the date if you’re storing date+time in your date field. > On 28 Dec 2019, at 8:45 am, Luuk wrote: > > >> On 28-12-2019 13:07, Csanyi Pal wrote: >> 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? >> >> > SQList is able to calulate averages. lets give simple example > > select min(a),avg(a),max(a) > from (select 1 as a union all select 2 union all select 3 union all select 4); > > It will output: > > 1|2.5|4 > > because 1 is the minimum values of the selected values 1,2,3,4 > > 2.5 is the average of the selected values 1,2,3,4 > > and 4 is the max values of those. > > > For calculating with date (and/or time) function you should read this page: > https://www.sqlite.org/lang_datefunc.html > > If you created an example that 'does not work' (i do mean 'that does not do > what you expect it to do' ), come back here with that example, and i'm sure > someone will help you > > post the database structure, the query and some example data, and if possible > the expected output... > > >> Do I think well? >> >> Any advices will be appreciated! >> > ___ > 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
Re: [sqlite] AVG Function HowTo
On 28-12-2019 13:07, Csanyi Pal wrote: 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? SQList is able to calulate averages. lets give simple example select min(a),avg(a),max(a) from (select 1 as a union all select 2 union all select 3 union all select 4); It will output: 1|2.5|4 because 1 is the minimum values of the selected values 1,2,3,4 2.5 is the average of the selected values 1,2,3,4 and 4 is the max values of those. For calculating with date (and/or time) function you should read this page: https://www.sqlite.org/lang_datefunc.html If you created an example that 'does not work' (i do mean 'that does not do what you expect it to do' ), come back here with that example, and i'm sure someone will help you post the database structure, the query and some example data, and if possible the expected output... Do I think well? Any advices will be appreciated! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] AVG Function HowTo
2019. 12. 28. 13:07 keltezéssel, Csanyi Pal írta: 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! If one can't find the attachment mentioned in the original mail, then here is the database in text: CREATE TABLE Expenses (Id INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE, Date DATE, Expense REAL); CREATE TABLE Earnings (Id INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE, Date DATE, Earning REAL, Part TEXT); CREATE VIEW "AVG_of_Expenses_BETWEEN_Dates_From_I._Earnings_To_Date_minus_1_Day_before_II._Earnings_in_a_Month" AS SELECT "Date", "Expense" FROM Expenses WHERE EXISTS(SELECT * FROM Earnings WHERE Earnings.Date = Expenses.Date) /* 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. */ /*
[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
