0

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.

Allison Grut
  • 1
  • 1

1 Answers1

0

You may use conditional aggregation here:

SELECT level, SUM(done = 1) AS total
FROM Users
GROUP BY level;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you so much Tim. When I execute the query via the App Inspector in Android Studio, it returns the correct information. However, when I try building with these changes I get a compile error : >The columns returned by the query does not have the fields [value] in java.lang.Integer even though they are annotated as non-null or primitive. Columns returned by the query: [level,total] . I do have a class with the annotation required by Room Db as follows: @ColumnInfo(name = "level") val level : Int, @ColumnInfo(name = "done") var done : Int, – Allison Grut Nov 10 '22 at 21:16
  • This solution works well. Thanks again Tim. The error was related to the return of two columns instead of one. My final query looks almost the same, but in order to return a singly linked list I used: SELECT SUM(done=1) As total From Users GROUP By level. – Allison Grut Nov 10 '22 at 21:27