Narendra Pal Narendra Pal - 2 months ago 5
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

Answer

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.