Sum query in sqlite in android
Solution 1
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.
Solution 2
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';
Hishighness731
Updated on October 10, 2020Comments
-
Hishighness731 over 3 years
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.
-
Hishighness731 over 11 yearsTried @toadsky style ,still force close
-
Sam over 11 yearsPlease read my comment on getting the LogCat and the rest of my answer.
-
Hishighness731 over 11 yearsbd mainly has oncreate() and on upgrade().DbCrud insert(),delete(),update(),retrieve()
-
Sam over 11 yearsWell then you need to initialize
db
. -
Hishighness731 over 11 yearsprivate static SQLiteDatabase db; in the second line
-
Sam over 11 yearsThat is where you define
db
, you need to initialize it withdb = ...
before trying to use it. -
Hishighness731 over 11 yearsThanks a ton....shifted the code to DbCrud and boom!!! it worked...Thanks again!!