SQLite queries failing in Android

Question

If I use the query to return some basic columns in table, that is OK. But if I use group by, order by , sum(column) or choose specified columns, the query fails to run.

I use Cursor query method or rawquery, both the same. If I run the following queries in adb shell, it works without problems.

select * from table (work)
select fielda, fieldb from table (failed)
select fielda, sum(fieldb) from table group by fielda (failed)

The following queries work without problems.

select * from table where strftime('%m',field)='09'
select * from table where strftime('%Y',field)='2010'

Anyone knows what happened to these query to make them fail? Are there any rules I need to follow?

Answer 1

My table contains six columns. (categories, item, description, price, time, flag) All queries can be returned in adb shell without problems.

Here is my finding. 1. based on month and year selected from Spinner, it will show the columns item, price and time in listview. time is in YYYY-MM-DD format. So I use select * from table where strftime('m%',time) ='05', select * from table where strftime('%Y',time)='2010'. It doesn't have problem. then I put cursor result into simplecursoradapter, then I put adapter into listview. It runs without problems

1. I want to show the view that summarized all the categories with total price for that categories. So I use select categories, sum(price) from table group by categories. I also try to use total(price) but it is still the same, program failed when I execute. I use rawquery or querybuilder but the result is the same.

2. If I don't use group by, order by, having, only include basic column (categories, item, description, price, time, flag) it doesn't have problems. If I use aggregate function, query will fail.

3. I tried to create layout from XML definition or from Java code dynamically. The problem still the same. But all the queries run fine under adb shell without problem.

So I don't know what happened here. Anyone can share your experience in sqlite in Android ?