CODEWITHSUNDEEP
androidsqlite
Narendra Pal
Narendra Pal

Reputation: 6604

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

Upvotes: 2

Views: 207

Answers (2)

johannes
johannes

Reputation: 7272

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.

Upvotes: 2

Ram kiran Pachigolla
Ram kiran Pachigolla

Reputation: 21191

Then you can find the months difference by using this method

private int monthsdiff(int m1,int year1, int m2,int year2)
{
    int yeardiff = year2 - year1;
    int mondiff  = m2 - m1;
    mondiff = yeardiff*12+mondiff;
    return mondiff;
}

Upvotes: 1

Related Questions