CODEWITHSUNDEEP
javaandroidsqlsqliteandroid-sqlite
Sourav Das
Sourav Das

Reputation: 21

SQLite aggregation query

I have one table of mobile phones with columns

Sample table:

id    brand_name model_name condition sell_status
1       Apple        6s       New      unsold
2       Apple        6s       Used     unsold
3       Apple        6s       New      unsold
4       Apple        5s       New      sold

I want result be like

     brand_name model_name Quantity New Used
       Apple        6s       3       2   1

I tried this but didn't get 'New' and 'Used' item result:

public List<Mobile> getBrandMobileModel(String brand_name) {

    List<Mobile> mobileData = new ArrayList<Mobile>();
    String selectAllQuery = "SELECT brand_name, model_name, count(*), count(condition = ?), count(condition = ?) FROM " + Table_Mobile_Details + " WHERE brand_name = ? AND sell_status = ? GROUP BY model_name";

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectAllQuery, new String[]{"New", "Used", brand_name, "unsold"});

    if (cursor.moveToFirst()) {
        do {

            Mobile mobile = new Mobile();

            //mobile.setId(cursor.getInt(0));
            mobile.setBrand_name(cursor.getString(1));
            mobile.setModel_name(cursor.getString(2));
            mobile.setQuantity(cursor.getString(3));
            mobile.setCondition_new(cursor.getString(4));
            mobile.setCondition_used(cursor.getString(5));

            mobileData.add(mobile);

        } while (cursor.moveToNext());
    }
    return mobileData;
}

Upvotes: 1

Views: 83

Answers (1)

forpas
forpas

Reputation: 164064

You need to GROUP BY both brand_name, model_name and use SUM with CASE to get the number of rows with values of 'New' and 'Used'

String selectAllQuery = 
    "SELECT brand_name, model_name, " +
            "count(*) as Quantity, " + 
        "SUM(CASE WHEN condition = 'New' THEN 1 ELSE 0 END) as New, " +
        "SUM(CASE WHEN condition = 'Used' THEN 1 ELSE 0 END) as Used " +
    "FROM " + Table_Mobile_Details + " WHERE brand_name = ? AND sell_status = ? GROUP BY brand_name, model_name";

Also you need 2 parameters, so:

Cursor cursor = db.rawQuery(selectAllQuery, new String[]{brand_name, "unsold"});

Upvotes: 2

Related Questions