CODEWITHSUNDEEP
sqlsqlitedatetimesum
nali
nali

Reputation: 491

Sum entries of an sqlite query

I have a table similar to this:

| id(INTEGER) | id2(INTEGER) |    time(DATETIME)    | value(REAL) |
|-------------|--------------|----------------------|-------------|
|     1       |      2000    |  2004-01-01 00:00:00 |    1000     |

which I query with visual basic. Now I want to sum all entries between year 2004 and 2010 so the result looks like this:

2004 11,000
2005 35,000
2006 46,000

cIf I do it inside visual basis it is achieved with few loops but unfortunately this is not very performant.

Is it possible to create a query which yields the result say between two years grouped by years. Or between two months (within one year), grouped by months, days (within one month), hours (within one day), minutes (within one hour)?

EDIT: Query for year interval:

SELECT STRFTIME('%Y', time) AS year, SUM(value) AS cumsum FROM mytable WHERE year >= STRFTIME('%Y','2005-01-01 00:00:00') AND year <= STRFTIME('%Y','2010-01-01 00:00:00') GROUP BY STRFTIME('%Y', mytable.time)  ORDER BY year;

Now need an idea for months, days and hours.

Upvotes: 0

Views: 83

Answers (2)

Swetha reddy
Swetha reddy

Reputation: 71

Hi Below is the code for both sql and sqlite.we can do group by time instead of using the strftime function again.

SQLITE: select strftime('%Y', time)as year , sum(value) value from attendence group by time

SQL: select EXTRACT(YEAR FROM time) as year, sum(value) value from attendence Group by time

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is an aggregation query, but you need to extract the year from the date:

select strftime('%Y', time) as yr, sum(value)
from table
group by strftime('%Y', time)
order by yr;

Upvotes: 2

Related Questions