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 <luu...@gmail.com> 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
