Example1.java

static final String CREATE_CATEGORIES_TABLE = "create table " + Category.TABLE + "(" +
       COLUMN_ID + " integer primary key autoincrement, " +
       Category.COL_NAME + " text, " +
       Category.COL_COLOR + " integer, " +
       Category.COL_CREATE_BY_USER + " integer DEFAULT 0 " +
       ");";

static String LIST_QUERY(String selection) {
   String query = "SELECT *, COUNT(i." + ListItem.COL_STATUS + ") " + List.COL_SIZE
           + ", SUM(i." + ListItem.COL_STATUS + ") " + List.COL_BOUGHT_COUNT +
           " FROM " + List.TABLE + " s" +
           " LEFT OUTER JOIN " + ListItem.TABLE + " i" +
           " ON s." + List.COL_ID + " = i." + ListItem.COL_PARENT_LIST_ID;
   if (selection == null) {
       return query + " GROUP BY s." + List.COL_ID;
   }
   return query + " WHERE " + selection +
           " GROUP BY s." + List.COL_ID;
}