How to query room database to get the count of all rows grouped by Level where done = 1

Question

I have a "Users" database table as follows:

Name	Level	Done
Allen	1	0
Diane	1	0
Victor	2	1
Gabriel	3	0
Roger	4	0
Julia	4	1

I want to write a query to return the count of all records, including null values where the value of done is 1.

I wrote a query to return the count of all records and grouped by level:

    SELECT COUNT(*) 
           FROM Users 
           GROUP BY level

My result is as expected:

    1  -> 2
    2  -> 1
    3  -> 1
    4  -> 2

I also want to write a query to return the count of all records, including null values where done = 1 so I expect the result to be

                            1  -> 0
                            2  -> 1
                            3  -> 0
                            4  -> 1

I tried:

    SELECT COUNT(*) 
           From Users 
           WHERE done = 1 
           GROUP BY level

but my result was :

    1 -> 1
    2 -> 1

because null values are being skipped

Any help will be appreciated.

Answer 1

You may use conditional aggregation here:

SELECT level, SUM(done = 1) AS total
FROM Users
GROUP BY level;