java - How to fire join query with count using SQLite multiple table in Android? -

java - How to fire join query with count using SQLite multiple table in Android? -

i'm creating android app sqlite database multiple table. have create 2 sqlite tables , want retrieve both tables column count.

here database helper code

@override public void oncreate(sqlitedatabase db) { // todo auto-generated method stub string create_contacts_table = "create table "+ contacts_table_name + "(" + project_id + " integer primary key autoincrement, " + project_name + " text," + project_date_created + " text, " + project_end_date+ " text, " + project_is_active + " text "+");"; db.execsql(create_contacts_table); string create_task_table = "create table " + assign_task_table + "(" + tsk_id +" integer primary key autoincrement, " + task_title + " text," + task_start_date + " text," + task_completiondate + " text," + task_completiontime + " text," + task_description + " text," + task_status + " text, " + task_isactive +" text, " + task_project_id + " integer," + task_team_memmber_id + " integer," + " foreign key ("+task_project_id+") references " +contacts_table_name+" ("+project_id+") on update cascade," + " foreign key ("+task_team_memmber_id+") references " +define_team_member_table+" ("+team_member_id+") on update cascade )"; db.execsql(create_task_table); } public list<string> gettaskdetails() { list arrlist=new arraylist (); seek { sqlitedatabase db=this.getwritabledatabase(); string strquery = "select p.project_name, " +" count(t.task_id) total_task " +" count(t.task_status "+"'c'"+" ) completed_task," +" count(t.task_status "+"'p'"+" ) workinprogress," +" count(t.completion_date) finishingtoday" +" assign_task t " +" inner bring together project p on t.task_project_id = p.project_id"; log.d("query message ", strquery); cursor c = db.rawquery(strquery, null); size=c.getcount(); strtaskprojectname=new string[size]; strenddate=new string[size]; strtotaltask=new string[size]; strcompletedtask=new string[size]; strworkinprogress=new string[size]; strfinishingtoday=new string[size]; int col1 = c.getcolumnindex("project_name"); int col2 = c.getcolumnindex("project_end_date"); int col3 = c.getcolumnindex("task_id"); int col4 = c.getcolumnindex("task_status"); int col5 = c.getcolumnindex("start_date"); int col6 = c.getcolumnindex("completion_date"); if (c != null) { c.movetofirst(); if( size > 0) { { strtaskprojectname[i]=c.getstring(col1); strenddate[i]=c.getstring(col2); strtotaltask[i]=c.getstring(col3); strcompletedtask[i]=c.getstring(col4); strworkinprogress[i]=c.getstring(col5); strfinishingtoday[i]=c.getstring(col6); system.out.println("strtaskprojectname :"+strtaskprojectname[i]); system.out.println("strenddate :"+strenddate[i]); system.out.println("strtotaltask :"+strtotaltask[i]); system.out.println("strcompletedtask :"+strcompletedtask[i]); system.out.println("strworkinprogress :"+strworkinprogress[i]); system.out.println("strfinishingtoday :"+strfinishingtoday[i]); i++; } while (c.movetonext()); arrlist.add(strtaskprojectname); arrlist.add(strenddate); arrlist.add(strtotaltask); arrlist.add(strcompletedtask); arrlist.add(strworkinprogress); arrlist.add(strfinishingtoday); } } db.close(); } catch(sqlitefullexception exp) { exp.getmessage(); log.d("exception cause", exp.getmessage()); } homecoming arrlist; } here activity code , want access column here public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.my_project); imgbuttonback = (imageview)findviewbyid(r.id.imagbackbutton);//imageview imgbuttonback; imgbuttonback.setonclicklistener(new onclicklistener() { @override public void onclick(view v) { // todo auto-generated method stub intent iback = new intent(my_project.this , menu.class); startactivity(iback); finish(); } }); explistview = (expandablelistview) findviewbyid(android.r.id.list); databasehelper = new dbhelper(getapplicationcontext()); dispdatalist=databasehelper.gettaskdetails(); dtrprojectnamesize=new string[dispdatalist.size()]; system.out.println(" dtrprojectnamesize = " + dtrprojectnamesize); if ( dispdatalist.size() > 2 ) { nameval=(string[])dispdatalist.get(0); enddate=(string[]) dispdatalist.get(1); totaltask = (string[])dispdatalist.get(2); for(int i=0;i<nameval.length;i++) { system.out.println("new info :"+nameval[i]); } } }

here log cat error info

06-20 09:57:15.831: d/query message(370): select p.project_name, count(t.task_id) total_task count(t.task_status 'c' ) completed_task, count(t.task_status 'p' ) workinprogress, count(t.completion_date) finishingtoday assign_task t inner bring together project p on t.task_project_id = p.project_id 06-20 09:57:15.831: i/database(370): sqlite returned: error code = 1, msg = near "count": syntax error 06-20 09:57:15.841: d/androidruntime(370): shutting downwards vm 06-20 09:57:15.841: w/dalvikvm(370): threadid=1: thread exiting uncaught exception (group=0x40015560) 06-20 09:57:15.850: e/androidruntime(370): fatal exception: main 06-20 09:57:15.850: e/androidruntime(370): java.lang.runtimeexception: unable start activity componentinfo{com.sentaca.android.accordion/com.sentaca.android.accordion.my_project}: android.database.sqlite.sqliteexception: near "count": syntax error: , while compiling: select p.project_name, count(t.task_id) total_task count(t.task_status 'c' ) completed_task, count(t.task_status 'p' ) workinprogress, count(t.completion_date) finishingtoday assign_task t inner bring together project p on t.task_project_id = p.project_id 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread.performlaunchactivity(activitythread.java:1647) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread.handlelaunchactivity(activitythread.java:1663) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread.access$1500(activitythread.java:117) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread$h.handlemessage(activitythread.java:931) 06-20 09:57:15.850: e/androidruntime(370): @ android.os.handler.dispatchmessage(handler.java:99) 06-20 09:57:15.850: e/androidruntime(370): @ android.os.looper.loop(looper.java:123) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread.main(activitythread.java:3683) 06-20 09:57:15.850: e/androidruntime(370): @ java.lang.reflect.method.invokenative(native method) 06-20 09:57:15.850: e/androidruntime(370): @ java.lang.reflect.method.invoke(method.java:507) 06-20 09:57:15.850: e/androidruntime(370): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:839) 06-20 09:57:15.850: e/androidruntime(370): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:597) 06-20 09:57:15.850: e/androidruntime(370): @ dalvik.system.nativestart.main(native method) 06-20 09:57:15.850: e/androidruntime(370): caused by: android.database.sqlite.sqliteexception: near "count": syntax error: , while compiling: select p.project_name, count(t.task_id) total_task count(t.task_status 'c' ) completed_task, count(t.task_status 'p' ) workinprogress, count(t.completion_date) finishingtoday assign_task t inner bring together project p on t.task_project_id = p.project_id 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitecompiledsql.native_compile(native method) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitecompiledsql.compile(sqlitecompiledsql.java:92) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitecompiledsql.<init>(sqlitecompiledsql.java:65) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqliteprogram.<init>(sqliteprogram.java:83) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitequery.<init>(sqlitequery.java:49) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitedirectcursordriver.query(sqlitedirectcursordriver.java:42) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitedatabase.rawquerywithfactory(sqlitedatabase.java:1356) 06-20 09:57:15.850: e/androidruntime(370): @ android.database.sqlite.sqlitedatabase.rawquery(sqlitedatabase.java:1324) 06-20 09:57:15.850: e/androidruntime(370): @ com.sentaca.android.accordion.dbhelper.gettaskdetails(dbhelper.java:278) 06-20 09:57:15.850: e/androidruntime(370): @ com.sentaca.android.accordion.my_project.oncreate(my_project.java:70) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 06-20 09:57:15.850: e/androidruntime(370): @ android.app.activitythread.performlaunchactivity(activitythread.java:1611) 06-20 09:57:15.850: e/androidruntime(370): ... 11 more 06-20 09:57:17.930: i/process(370): sending signal. pid: 370 sig: 9

there missing comma behind total_task.

java android sqlite