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
