column doesn't exists exception when trying to sum in sqlite query in android

Question

I am using SQlite database to store data.. but when I try to sum the columns using query I am getting error Column doesn't exists like below:

select _id, column1, sum(column2),sum(column3) from table

At the same time when I write without sum I am able to see the result in application as below.

select _id, column1, column2,column3 from table

Column2 and Column3 are Real data type columns.

I have no clue about this behaviour, why it is behaving this way.

Code to retrieve the columns from cursor.

summaryviewholder.Total.setText(String.valueOf(getsummary.getInt(getsummary.getColumnIndexOrThrow(st.column2))));

Logcat:

E/AndroidRuntime(8187): FATAL EXCEPTION: main
06-25 15:53:24.011: E/AndroidRuntime(8187): Process: com.example.portfoliomanager, PID: 8187
06-25 15:53:24.011: E/AndroidRuntime(8187): java.lang.IllegalArgumentException: column 'Buy_Qua' does not exist
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.example.StockDirector.stockmanager$popsummarystock.bindView(stockmanager.java:518)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.example.StockDirector.stockmanager$popsummarystock.newView(stockmanager.java:539)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.CursorAdapter.getView(CursorAdapter.java:250)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.AbsListView.obtainView(AbsListView.java:2720)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.ListView.measureHeightOfChildren(ListView.java:1274)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.ListView.onMeasure(ListView.java:1186)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.RelativeLayout.measureChild(RelativeLayout.java:689)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.RelativeLayout.onMeasure(RelativeLayout.java:473)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5352)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1410)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.LinearLayout.measureVertical(LinearLayout.java:695)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.LinearLayout.onMeasure(LinearLayout.java:588)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5352)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5352)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.android.internal.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:391)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:5352)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.android.internal.policy.impl.PhoneWindow$DecorView.onMeasure(PhoneWindow.java:2533)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.View.measure(View.java:17387)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2214)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1351)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1550)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1235)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:6476)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.Choreographer$CallbackRecord.run(Choreographer.java:803)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.Choreographer.doCallbacks(Choreographer.java:603)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.Choreographer.doFrame(Choreographer.java:573)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:789)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.os.Handler.handleCallback(Handler.java:733)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.os.Handler.dispatchMessage(Handler.java:95)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.os.Looper.loop(Looper.java:157)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at android.app.ActivityThread.main(ActivityThread.java:5356)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at java.lang.reflect.Method.invokeNative(Native Method)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at java.lang.reflect.Method.invoke(Method.java:515)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1265)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1081)
06-25 15:53:24.011: E/AndroidRuntime(8187):     at dalvik.system.NativeStart.main(Native Method)
06-25 15:53:25.486: I/Process(8187): Sending signal. PID: 8187 SIG: 9

Answer 1

You have to name your rows you summarize.

Other notes:

If you use sum (or aggregation function in general), you can't select other rows that are not aggregated, or else you won't get a proper result. Also, use Float for getting the value from the database, as you use the Real datatype (floating point number) in sqlite.

So instead of this:

select _id, column1, sum(column2),sum(column3) from table

You have to do something like this:

select column1, sum(column2) AS sum1, sum(column3) AS sum2 from table group by column1;

And to the values:

summaryviewholder.Total.setText(String.valueOf(getsummary.getFloat(getsummary.getColumnIndexOrThrow("sum1"))));

Answer 2

When you write SELECT sum(Column2), then the name of the output column is sum(Column2).

You should either read the column values by their index (which is dangerous when the query ever changes), or give the output column another name:

SELECT sum(Column2) AS Column2Sum ...