Sum query in sqlite in android

Question

I'm new to java,android and sqlite and i'm stuck at this. I have columns namely _id,type,amount(int),category,description.what i'm trying is to fetch the sum of amount of a particular category and store them in int. i know i need to provide the errors but it is simply giving me force close.Please Help

 public class TransactionReport extends Activity {
private static SQLiteDatabase db;
Cursor c;
int amount;
DbCrud localDbCrud = new DbCrud(this);

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.report);
    localDbCrud.open();                //localDatabase = localDbHelper.getWritableDatabase();  return this;
    c.moveToFirst();
    do{
        c = db.rawQuery("select sum(amount) from transaction_table where category = Salary ;", null);


      }while(c.moveToNext());

    amount = c.getInt(0);
    c.close();
    TextView ltxt = (TextView) findViewById(R.id.amt);
    ltxt.setText(""+amount);
    localDbCrud.close();  //localDatabase.close();


}

}

Would be helpful if explained in detail as i am a complete noob and i did try to find the solution but didnt find any.Thanks in advance

10-10 03:55:29.828: E/AndroidRuntime(10229): FATAL EXCEPTION: main
10-10 03:55:29.828: E/AndroidRuntime(10229): java.lang.RuntimeException: Unable to      start activity   ComponentInfo{com.hishighness.budgetracker/com.hishighness.budgetracker.TransactionReport}:     java.lang.NullPointerException
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1651)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1667)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at  android.app.ActivityThread.access$1500(ActivityThread.java:117)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:935)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.os.Handler.dispatchMessage(Handler.java:99)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.os.Looper.loop(Looper.java:130)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.app.ActivityThread.main(ActivityThread.java:3687)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at java.lang.reflect.Method.invokeNative(Native Method)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at java.lang.reflect.Method.invoke(Method.java:507)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:842)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:600)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at dalvik.system.NativeStart.main(Native Method)
10-10 03:55:29.828: E/AndroidRuntime(10229): Caused by: java.lang.NullPointerException
10-10 03:55:29.828: E/AndroidRuntime(10229):    at com.hishighness.budgetracker.TransactionReport.onCreate(TransactionReport.java:27)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at  android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
10-10 03:55:29.828: E/AndroidRuntime(10229):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1615)
10-10 03:55:29.828: E/AndroidRuntime(10229):    ... 11 more

Here's the log.

"i know i need to provide the errors but it is simply giving me force close" If you experience a force close, you have LogCat errors. In Eclipse, go to **Window** -> **Show View** -> **LogCat**, then cut & paste the red, error lines into your question. Otherwise we can only guess... — Sam, Oct 09 '12 at 22:26
"Caused by: java.lang.NullPointerException at com.hishighness.budgetracker.TransactionReport.onCreate(TransactionReport.java:27)" Which is line 27? — Sam, Oct 09 '12 at 22:34
line 27 c = db.rawQuery("select sum(amount) from transaction_table where category = 'Salary' ;", null); — Hishighness731, Oct 09 '12 at 22:37

Sam · Accepted Answer · 2012-10-09T22:41:10.663

14

Addition
I believe you want to use localDBCrud.rawQuery(), not db.rawQuery().

Original
I'm guessing that you have a NullPointerException. Here:

c.moveToFirst();

You are try to use c before you have initialized it with db.rawQuery().

You have some more logic errors here:

c.moveToFirst();
do{
    c = db.rawQuery("select sum(amount) from transaction_table where category = Salary ;", null);
  }while(c.moveToNext());

amount = c.getInt(0);
c.close();

Try this instead:

c = db.rawQuery("select sum(amount) from transaction_table where category = Salary ;", null);
if(c.moveToFirst())
    amount = c.getInt(0);
else
    amount = -1;
c.close();

Also @toadsky has made a great observation, follow his advice too.

edited Oct 09 '12 at 22:41

answered Oct 09 '12 at 22:18

Sam

86,580
20
181
179

Tried @toadsky style ,still force close – Hishighness731 Oct 09 '12 at 22:26
Please read my comment on getting the LogCat and the rest of my answer. – Sam Oct 09 '12 at 22:27
bd mainly has oncreate() and on upgrade().DbCrud insert(),delete(),update(),retrieve() – Hishighness731 Oct 09 '12 at 22:45
Well then you need to initialize `db`. – Sam Oct 09 '12 at 22:46
private static SQLiteDatabase db; in the second line – Hishighness731 Oct 09 '12 at 22:47
That is where you _define_ `db`, you need to _initialize_ it with `db = ...` before trying to use it. – Sam Oct 09 '12 at 22:48
Thanks a ton....shifted the code to DbCrud and boom!!! it worked...Thanks again!! – Hishighness731 Oct 09 '12 at 23:04

score 1 · Answer 2 · answered Oct 09 '12 at 22:18

1

You should wrap string values in single quotes. so instead of:

select sum(amount) from transaction_table where category = Salary ;

do:

select sum(amount) from transaction_table where category = 'Salary';

answered Oct 09 '12 at 22:18

toadzky

3,806
1
15
26

Sum query in sqlite in android

2 Answers2