Add all records before a specific date - Sqlite

Asked

Viewed 387 times

1

I am wanting to add up values from several rows of my table (add up expenditure values) prior to a specific date (for example, add up all values that are lower than the date 2015-26-06). I’m using the following code, but it’s not functional.

SELECT SUM (VALOR) FROM despesa 
  WHERE strftime('%Y-m%-d%', data) <= strftime('%Y-m%-d%', '2015-06-30')
  AND pago = 1 AND idusuario = 1

Could someone guide me in this? I thank you in advance.

Structure of the Expenditure table

tabela despesa

android sqlite

  • "but he’s not functional." What do you mean by that?

  • that it returns me null, that is, it adds nothing, it is not functional.. @Paulohdsousa

  • it is functional, you are passing the parameter wrong way. just convert the date correctly. The base is populated?

  • is yes, I copied it from the emulator to test the commands using Sqlite Browser, would you have any conversion tips? @Paulohdsousa

  • @Paulohdsousa would have some way to search without the day? only use year and month?

  • Yes, there is a way.

  • and how would that be? @Paulohdsousa

Show 2 more comments

2 answers

1


Do so

SELECT SUM (VALOR) FROM despesa WHERE data <= Datetime('2015-06-30 00:00:00') 
  AND pago = 1 AND idusuario = 1

  • Very good Paulohdsousa, had no knowledge of this Datetime on sqlite, thank you

1

The error is here: strftime('%Y-m%-d%', data)

The function strftime(format, timestring, modifier, modifier, ...) only accepted string as second argument and you are passing a DATETIME.

The correct is the following: data <= strftime('%Y-m%-d%', '2015-06-30') in accordance with documentation.

Another cause that may return null is when the query does not return records.

Browser other questions tagged android sqlite

You are not signed in. Login or sign up in order to post.