sqlmysql

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;