CODEWITHSUNDEEP
androidsqlitelistadapter
Debora Carnevali
Debora Carnevali

Reputation: 28

Android SQLite query group by and order by

I insert data in a listview with the spinner and choose the year you want and visualize the data based on the selected date. Now I want to group the data by year and not separated by month. What should I change? this is the code:

private void showDetails(String anno){
    SQLiteDatabase db = new BilancioHelper(this).getReadableDatabase();
    final List<Dettaglio> dettagli = new ArrayList<Elenco_cat_entrate.Dettaglio>();

    for (int i=1; i<=12; i++){
        String mese;
        if (i<10){
            mese = "0"+i;
        } else {
            mese = ""+i;
        }
        String sql ="SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria";
        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()){
            Dettaglio d = new Dettaglio();

            d.categorie = c.getString(0);
            d.entrate = c.getFloat(1);


            dettagli.add(d);
        }
        c.close();
    }

    db.close();

    ListAdapter adapter = new ArrayAdapter<Dettaglio>(this, R.layout.dettaglio_categorie_entrate, R.id.tv_totale_group, dettagli){

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            View row = super.getView(position, convertView, parent);


            TextView tvEntrata, tvCategoria;
            tvEntrata = (TextView) row.findViewById(R.id.tv_totale_group);

            tvCategoria = (TextView) row.findViewById(R.id.tv_categorie_group);


            Dettaglio d = dettagli.get(position);

            tvCategoria.setText(d.categorie+"");
            tvEntrata.setText(d.entrate+"");

            return row;
        }
    };

    lista.setAdapter(adapter);

}

Upvotes: 3

Views: 9253

Answers (1)

jboi
jboi

Reputation: 11892

Just keep out the mese part. The % in a like clause is a wildcard for any characters.

    String sql = "SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria";

I would additionally sort the output:

    String sql = "SELECT Categoria, SUM(Entrata) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria ORDER BY 2 DESC";

to sort by sum(entrata) with highest sums first. Or:

    String sql = "SELECT Categoria, SUM(Entrata), MIN(data) FROM Giornate WHERE entrata>0 AND data LIKE '"+anno+"-%' GROUP BY Categoria ORDER BY 3 DESC";

to sort by date with latest date first.

Upvotes: 2

Related Questions