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

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               |

I don't even know how to describe the question. So I got no any search result in Google and Stackoverflow. (Because of My poor English, maybe)

CodePudding user response：

Use conditional aggregation:

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

CodePudding user response：

if I under stand you want show sum of newComments Count and all comments so you can do it like

   SELECT  SUM ( CASE WHEN isRead=false THEN 1 ELSE 0 END ) AS newComment,
       Count(*) AS AllComments From comments where relatedRecord=1

also you can make store procedure for it.

CodePudding user response：

To place two result sets horizontally, you can as simple as use a subquery for an expression in the SELECT CLAUSE as long as the number of rows from the result sets match:

select (select count(*) from c_table where isread=false and relatedRecordId=1 ) as newCommentCount, 
count(*) as allCommentCount 
from c_table where relatedRecordId=1;