title: using sum and count inside case sql statement
body: <p>I want to do count for each row from table but I get this error:</p>
  
  <blockquote>
    <p>Can not perform an aggregate function on an expression containing an
    aggregate or a subquery.</p>
  </blockquote>
  
  <pre><code>   SELECT     TOP (100) PERCENT ISNULL(MAX(Id), 0) AS ID, 
             MONTH(p.datep) AS nMonth, 
             DATENAME(mm, p.dateP) AS month, 
             YEAR(p.dateP) AS year, c.EId, e.SId,
             e.nE, s.libelle,
             SUM(p.a) AS a, 
             SUM(p.a - p.b) AS c, 
             SUM(p.b) AS b, 
             SUM(p.dureePM + p.dureeAM) AS dX,
             SUM(CASE WHEN p.dureePm != 0 THEN 1 ELSE 0 END) AS nbrPm, 
             SUM(CASE WHEN p.dureeAm != 0 THEN 1 ELSE 0 END) AS nbrAm, 
             COUNT(CASE WHEN p.nbX &gt; 2 THEN  SUM(p.nbX-2)  ELSE 0 END) AS nbrXSupp, 
             COUNT(CASE WHEN p.nbY &gt; 2 THEN  SUM(p.nbY-2)  ELSE 0 END) AS nbrYSupp
  
  FROM       dbo.table AS p 
  INNER JOIN
             dbo.tableC AS c ON p.cId = c.CId 
  INNER JOIN
             dbo.tableE AS e ON c.EId = e.EId 
  INNER JOIN
              dbo.tableS AS s ON e.SId = s.SId
  
  GROUP BY MONTH(p.dateP), DATENAME(mm, p.dateP), YEAR(p.dateP), 
            c.EId, e.SId, e.nE, s.libelle
  </code></pre>