Sum two columns in SQL and optimise SQL query-CodePudding

I have the following query which returns two columns, I want to sum both columns and create the third column through summing the two.

Is there any way I can recreate the below query by removing the subquery? Any way I can achieve the same through joins?

SELECT
    IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
    (SELECT COUNT(c1.content)
     FROM comments c1
     WHERE c1.parentcommentid = c2.id
       AND c1.isdeleted = 0
       AND c1.approved = 1) ChildContentcount --Anyway to remove the subquery
FROM 
    comments c2 
WHERE  
    c2.discussionid = '257943' 
    AND c2.parentcommentid IS NULL
ORDER BY 
    c2.pinned DESC,
    c2.createddate

Sample data:

 ---------- -------------- 
| content  | childcontent |
 ---------- -------------- 
|        1 |            8 |
|        0 |            0 |
|        1 |            3 |
 ---------- --------------

Expected output:

 ---------- ---------------- --------- 
| content  | childcontent   | sumdata |
 ---------- ---------------- --------- 
|        1 |              8 |       9 |
|        0 |              0 |       0 |
|        1 |              3 |       4 |
|        1 |              8 |       9 |
 ---------- ---------------- ---------

CodePudding user response：

You can use CROSS APPLY or OUTER APPLY instead of a correlated subquery.
Then you can re-use the values.

select c.pinned, c.createddate
, c.discussionid
, ca1.content
, ca2.childcontent
, (ca1.content   ca2.childcontent) AS sumdata
FROM comments c
CROSS APPLY 
(
 SELECT CASE
        WHEN c.isdeleted = 1 OR c.approved = 0 THEN 0
        ELSE 1
        END AS content
) ca1
CROSS APPLY 
(
 SELECT COUNT(c2.content) AS childcontent
 FROM comments c2
 WHERE c2.parentcommentid = c.id
   AND c2.isdeleted = 0
   AND c2.approved = 1
) ca2
WHERE c.discussionid = '257943' 
  AND c.parentcommentid IS NULL 
ORDER BY 
  c.pinned DESC,
  c.createddate;

CodePudding user response：

Subquery and sum the columns :

    select tbl.* , Contentcount ChildContentcount third_sum from 
(
select   IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
    (SELECT COUNT(c1.content)
     FROM comments c1
     WHERE c1.parentcommentid = c2.id
       AND c1.isdeleted = 0
       AND c1.approved = 1) ChildContentcount 
FROM 
    comments c2 
WHERE  
    c2.discussionid = '257943' 
    AND c2.parentcommentid IS NULL ) tbl

If you supply sql fiddle, we can try to create it alternative ways