CODEWITHSUNDEEP
androidsqliteselect
d2000k
d2000k

Reputation: 29

sqlite select sum multiple with different where and same table

you can make multiple queries in a single query. I have a same table, different where, and multiple select sum: wrong code:

    public Cursor listado_resumen(String s_mes, String s_ano) {
    String s_actual = "-" + s_mes + "-" + s_ano;
    String s_actual_ano = "-" + s_ano;
    String lista_re = "SELECT *, (SELECT SUM (tr_euros) FROM bdt_registro WHERE tr_fecha LIKE '_%" + s_actual_ano + "' GROUP BY tr_codigo ORDER BY tr_codigo) AS total, (SELECT SUM (tr_euros) FROM bdt_registro WHERE tr_fecha LIKE '_%" + s_actual + "' GROUP BY tr_codigo ORDER BY tr_codigo) AS actual FROM bdt_registro GROUP BY tr_codigo ORDER BY tr_codigo";        
    return db.rawQuery(lista_re, null);
}

return:
only the first value

cod.||descrip||total||actual(month)

a ||house || 100 || 100
b ||car || 100 || 100
c ||food || 100 || 100

I like return:
cod.||descrip||total||actual (month)
a ||house || 100 || 20
b ||car || 20 || 5
c ||food || 5 || 0

Thanks for your interest

Upvotes: 0

Views: 1191

Answers (1)

CL.
CL.

Reputation: 180020

A scalar subquery must return only one value. (SQLite ignores any additional values; other databases might report an error instead.)

You have to write the subqueries so that they return the correct value for the corresponding record in the outer table, i.e., they must be correlated subqueries (I assume that the tr_codigo value can be used for the correlation):

SELECT *,
       (SELECT SUM (tr_euros)
        FROM bdt_registro AS r1
        WHERE tr_fecha LIKE '_%...'
          AND r1.tr_codigo = bdt_registro.tr_codigo
       ) AS total,
       (SELECT SUM (tr_euros)
        FROM bdt_registro AS r2
        WHERE tr_fecha LIKE '_%...'
          AND r2.tr_codigo = bdt_registro.tr_codigo
       ) AS actual
FROM bdt_registro
GROUP BY tr_codigo
ORDER BY tr_codigo

Upvotes: 0

Related Questions