[SOLVED] How to select both sum value of all rows and values in some specific rows?

How to select both sum value of all rows and values in some specific rows?

I have a record table and its comment table, like:

| commentId | relatedRecordId | isRead |
|-----------+-----------------+--------|
| 1         | 1               | TRUE   |
| 2         | 1               | FALSE  |
| 3         | 1               | FALSE  |

Now I want to select newCommentCount and allCommentCount as a server response to the browser. Is there any way to select these two fields in one SQL?

I've tried this:

SELECT `isRead`, count(*) AS cnt FROM comment WHERE relatedRecordId=1 GROUP BY `isRead`

| isRead | cnt |
| FALSE  | 2   |
| TRUE   | 1   |

But, I have to use a special data structure to map it and sum the cnt fields in two rows to get allCommentCount by using an upper-layer programming language. I want to know if I could get the following format of data by SQL only and in one step:

| newCommentCount | allCommentCount |
|-----------------+-----------------|
| 2               | 3               |

Solution

Use conditional aggregation:

SELECT SUM(NOT isRead) AS newCommentCount, COUNT(*) AS allCommentCount
FROM comment
WHERE relatedRecordId = 1;