Narendra Pal - 1 year ago 51
Android Question

Sqlite query for dealing with the date

I have the data in the Sqlite DB in this format..

``````  month     year     FarmerCode     Value
12       2012        1             10
9        2012        1             25
12       2012        2             5
10       2012        2             12
..........
..........
``````

I have to find out the average value with respect to FarmerCode. Suppose for FarmerCode 1, the two months are
`12 and 9`
. SO the diffrence is
`12 - 9 = 3`

And then the
`average = (25+10)/diffrence(which is 3 here)`

What will be the query to find this?

And at last if the date moves to next year as shown here

``````  month     year     FarmerCode     Value

1        **2013**        4             20
10       **2012**        4             50
``````

This time I can't do
`1-10`

So what will be the possible sqlite query to find the difference between two months as per the FarmerCode and the corresponding average, the formula to find the average will be

``````average = (value1+value2)/diffrence of months
``````

The idea:

The trick is to multiply the year by 12 and add it up with the month.

``````(2013*12+1)-(2012*12+10) = 3
``````

The SQL query:

A working SQL query tested in sqlite for this is:

``````SELECT
FarmerCode,
SUM(value) / (MAX(12 * year + month) - MIN(12 * year + month))
FROM
t
GROUP BY
FarmerCode;
``````

By using `GROUP BY FarmerCode` you get one result row per different `FarmerCode` value. `sum()`, `min()`, and `max()` aggregates therefore all values of rows with equal `FarmerCode`. The `min()` and `max()` construct in this query works properly, if there are two rows for each `FarmerCode` value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download