B4J Question [Solved] SqlLite , How Select data per month ??

behnam_tr

Active Member
Licensed User
hello my friends

3.png


I have the following database table and I want to extract the Monthly profit (from the profit column) information with the optimal code.
Does anyone have a solution ??

In fact, how many profit did we have per month?

like this :

Month 1 >> 250,000 $
Month 2 >> 300,000 $
.
.
.
.
.

all date values stored with DateTime.Now
We assume that the information has been stored from 1/1/2021 until today

thanks.
 

behnam_tr

Active Member
Licensed User
Do this to your date column and in a second query use it to group by month


many thanks
after many search and test i write this query
and its work fine

B4X:
    Dim RS As ResultSet=Sql1.ExecQuery($"SELECT strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month,
SUM( profit ) AS sum FROM tbl2 GROUP BY month;"$)
    Do While RS.NextRow
   
        Log(RS.GetString("year"))
        Log(RS.GetString("month"))
        Log(RS.GetString("sum"))
        Log("====================")
    Loop
    RS.Close


*** if you used UnixTime you should use this
strftime('%Y', date)
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
many thanks
after many search and test i write this query
and its work fine

B4X:
    Dim RS As ResultSet=Sql1.ExecQuery($"SELECT strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month,
SUM( profit ) AS sum FROM tbl2 GROUP BY month;"$)
    Do While RS.NextRow
 
        Log(RS.GetString("year"))
        Log(RS.GetString("month"))
        Log(RS.GetString("sum"))
        Log("====================")
    Loop
    RS.Close


*** if you used UnixTime you should use this
strftime('%Y', date)
If you have data more than 1 year, you should group by Year and Month.
 
Upvote 0
Top