android - Distinct values and count in MySQL -

android - Distinct values and count in MySQL -

i have table contains messages have been saved orm database.i have messages table , users table using fetching , saving values.

this sql query:

select distinct ch.user _id, con.name, con.image, con.color, con.contact_id messages ch inner bring together users con on con.phone = ch.user order ch.timestamp asc

i have boolean field status in messages table. want count of rows have status = false.

can integrated in above prepared query, can results in single query? or need execute 1 fetching count of rows?

you utilize sub select (check this fiddle):

select distinct ch.user _id, con.name, con.contact_id, (select count(*) messages ch2 status = false , ch.user = ch2.user) my_count messages ch inner bring together users con on con.phone = ch.user order ch.timestamp asc;

or you select result of select, might improve in terms of performance.

android sql android-sqlite