How can i get count of number of rows that have boolean value true(or 1) in Room persistent database without using SELECT query?

Question

I am working with Room persistent database in my project. I have a table in which there is a column for Boolean values as in 0 or 1, now i want the count of all Boolean values whose value is true (or 1).

I know that i can achieve this using select query by getting the count of all selected rows using where clause!

But i don't want to use Select query with where clause for this because it will load all the rows and then i will get the count, but i want the count without loading any rows! Suggest other simple solutions please! Thank you!

@NorthernPoet It is not actually! Because Room persistent database is used in android! — Priyanka Alachiya, Dec 18 '17 at 08:01

score 44 · Accepted Answer · answered Jan 05 '18 at 13:42

44

Finally I got the perfect solution! Just add this method in the DAO class as follows:

@Query("SELECT COUNT(is_checked) FROM table WHERE is_checked = 1")
int getNumberOfRows();

All thanks to Florina Muntenescu at https://medium.com/@florina.muntenescu

answered Jan 05 '18 at 13:42

Priyanka Alachiya

1,707
2
20
31

2

For reference, I believe the thanks is for [a comment](https://medium.com/@alachiyapriyanka/hi-maam-do-you-know-how-can-i-get-total-number-of-rows-in-a-table-using-room-database-without-643dbdb63998) on [this Medium article](https://medium.com/google-developers/7-steps-to-room-27a5fe5f99b2) – methodsignature Jan 09 '18 at 13:13
1

Wow, That was a spot-on! Worked like a charm. – sud007 Feb 26 '19 at 18:32

Northern Poet · Answer 2 · 2017-12-19T00:22:15.897

0

Using aggregate function sum may help you:

select
    sum(
        case
            when t.VALUE = 1 then
                1
            else
                0
        end
    )
from
    table t

Please note, SQLite engine will read entire table, if no indexed by aggregating field. It's no problem if there only few records in the table, otherwise it's better to consider using from keyword of indexed fields.

edited Dec 19 '17 at 00:22

answered Dec 18 '17 at 07:59

Northern Poet

1,955
1
12
17

Can u elaborate the answer please? I am using Room persistent library and it is not allowing me to write this query in my DAO class with @Query annotation! – Priyanka Alachiya Dec 18 '17 at 09:28
@NorthernPoet hey, i tried this solution again! And it worked with some spacing efforts and correcting your query with THEN keyword! So thank you so much :) – Priyanka Alachiya Dec 18 '17 at 10:17
Glad to know that it helps you. I made little edit to add details and correct `from` keyword missing for further users. – Northern Poet Dec 19 '17 at 00:23
What if we need 2 counts if Value = 1 and if Value = 2? – Yousuf Sohail Jun 01 '18 at 10:38
1

What syntax is this? – Afzal N Oct 18 '18 at 01:48

How can i get count of number of rows that have boolean value true(or 1) in Room persistent database without using SELECT query?

2 Answers2

Linked