title: : Select query with sum() as subquery
body: <p>I have a query that I need to add a column to. I can find many similar examples but nothing quite works right for this situation. There could be infinite CategoryIDs and I need to add the Sum(IsOutage) grouped by CategoryID. This is some sample data:</p>
  
  <p><a href="https://i.stack.imgur.com/pBC0B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pBC0B.png" alt="enter image description here"></a></p>
  
  <p>and here is the closest I have to getting it to work:</p>
  
  <pre><code>SELECT     c.CategoryID
  , c.Name
  , COUNT(i.IssueID) AS TotalIssues
  , AVG(DATEDIFF(MI, i.StartDate, i.ResolvedDate)) AS AvgDuration
  ,(Select COUNT(h.IssueID) From dbo.hdIssues AS h Where h.CategoryID = i.CategoryID AND IsOutage = 1 ) AS TotalOutages
  
  FROM         dbo.hdCategories AS c INNER JOIN
                    dbo.hdIssues AS i ON c.CategoryID = i.CategoryID
  WHERE     (i.StatusID = 3)
  GROUP BY c.CategoryID, c.Name, TotalOutages
  </code></pre>
  
  <p>Am I close? Please advise and thanx in advance</p>
  
  <p><strong>EDIT</strong>: This is a mock up of what the result should look like:</p>
  
  <p><a href="https://i.stack.imgur.com/DHRuZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DHRuZ.png" alt="enter image description here"></a></p>