How to sum up all rows total count?

Question

I'm using the following query, I need to show Grand Total count but it is throwing error like

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT  
      ISNULL(OQ.GroupID,'') GroupName,
      CONVERT(VARCHAR, ISNULL(COUNT(CASE WHEN RequestStatusKey IN ( 1, 2 ) THEN OrderRecordID END), 0)) TotalRecord,
      SUM(COUNT(CASE WHEN RequestStatusKey IN ( 1, 2 ) THEN OrderRecordID END)) AS GrandTotal
FROM dbo.tblDesk OQ                     
WHERE OQ.RequestStatusKey IN ( 1, 2 ) 
      AND OQ.OrderTypeKey <> 1 
      AND OQ.GroupID IS NOT NULL                                        
GROUP BY OQ.GroupID
ORDER BY OQ.GroupID

I just need to Grand total.

I'm unclear what you're looking for. Do you want a grand total like what you'd get using WITH ROLLUP or do you want a separate column that counts the totals altogether (which you could achieve with something like an SUM(COUNT(...)) OVER())? — ZLK

Himanshu Jansari Himanshu Jansari · Accepted Answer · 2019-06-26T05:29:45

As the error message suggests, you can not use aggregate function inside another aggregate function.

For your query to achieve SUM of OrderRecordId when RequestStatusKey IN (1,2) you can use SUM without using COUNT like this:

SUM(CASE WHEN RequestStatusKey IN (1,2) THEN 1 ELSE 0 END) AS GrandTotal

However, as Tim suggested, since you have already used RequestStatusKey IN (1,2) in your WHERE clause you don't need to use conditional SUM. Just use COUNT without condition:

COUNT(OrderRecordId) AS GrandTotal

UPDATE:

Since you want to show sum of the all rows count in the same result, you can use ROLLUP for that:

SELECT  
      ISNULL(OQ.GroupID,'Grand Total') GroupName,
      CONVERT(VARCHAR, COUNT(OrderRecordID)) TotalRecord
FROM tblDesk OQ                     
WHERE OQ.RequestStatusKey IN ( 1, 2 ) 
      AND OQ.GroupID IS NOT NULL                                        
GROUP BY ROLLUP (OQ.GroupID)
ORDER BY OQ.GroupID

See this SQLFiddle.

How to sum up all rows total count?

1 Answers