CODEWITHSUNDEEP
androidsql
Rui Miranda
Rui Miranda

Reputation: 319

i need to SUM column values for the curren month only

Hi everyone i need to get a SUM query to sum only the values from the current month

is there anyway to do that? 

public  int getTotal() {
    // TODO Auto-generated method stub
        int sum=0;
        Cursor cursor = ourDatabase.rawQuery(
                "SELECT SUM(_value) FROM TableActone", null);
            cursor.moveToFirst();
            if(cursor.getCount()>0) {
                sum=cursor.getInt(0);
            }
            return sum;
           }

I have this right now, but it gets the SUM of every value on that column !

I want just to sum up the value for november 2013 for instance.

Anyway to do that?

TY all in advance

Edit:

public static final String KEY_DATEACTONE = "_date";

i have a column where the users inputs the date of the expense. (Its a expense manager app)

The user inputs the date of the expense and i use this method to get the date into de database

final DatePickerDialog.OnDateSetListener mydate = new DatePickerDialog.OnDateSetListener() {

        @Override
        public void onDateSet(DatePicker view, int year, int monthOfYear,
                int dayOfMonth) {
            // TODO Auto-generated method stub
            myCalendar.set(Calendar.YEAR, year);
            myCalendar.set(Calendar.MONTH, monthOfYear);
            myCalendar.set(Calendar.DAY_OF_MONTH, dayOfMonth);
            updateLabel();
        }

    };

    date.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            new DatePickerDialog(ActoneSQLentry.this, mydate, myCalendar
                    .get(Calendar.YEAR), myCalendar.get(Calendar.MONTH),
                    myCalendar.get(Calendar.DAY_OF_MONTH)).show();
        }
    });

}

private void updateLabel() {

    String myFormat = "dd/MM/yy"; // In which you need put here
    SimpleDateFormat sdf = new SimpleDateFormat(myFormat, Locale.US);

    date.setText(sdf.format(myCalendar.getTime()));

Upvotes: 0

Views: 184

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21667

You have to give it the current month and year. Try this:

SELECT SUM(_value)
FROM TableActone
WHERE strftime('%m', date_col) = strftime('%m', (
      SELECT DATE ('now')
      ))
  AND strftime('%Y', date_col) = strftime('%Y', (
      SELECT DATE ('now')
      ))

You can see the docs for strftime here.

sqlfiddle demo

Upvotes: 1

Related Questions