title: MySQL Case Statement with aggregate function
body: <p>I am stuck at MySQL CASE statement. I have following scenario and my table has below records:  </p>
  
  <pre><code>Header_ID   |  Marks  
  ------------|----------  
  55          |   0  
  55          |   2  
  66          |   5  
  66          |   6  
  </code></pre>
  
  <p>I want to sum up the Marks, Header_ID wise. But for any given header, if the Marks is 0 (ZERO) then the SUM must be set to 0. So result should be as below:  </p>
  
  <pre><code>Header_ID   | TOTAL_Marks  
  ------------|------------  
  55          |    0 --&gt; One of the Header has 0 marks so I must have 0 here.  
  66          |    11 --&gt; This is sum of Marks for Header_ID 66  
  </code></pre>
  
  <p>I tried using CASE statement with "SUM" function but getting the result as below:  </p>
  
  <pre><code>SELECT  
      S.`headerid` AS `HEADER_ID`,  
      CASE  
        WHEN S.`mark` = 0 THEN 0  
        ELSE SUM(S.`mark`)  
      END AS `TOTAL_MARKS`  
  FROM sample_db.sheet_data S  
  GROUP BY S.`headerid`;  
  
  RESULT:  
  HEADER_ID  |  TOTAL_MARKS  
  -----------|-------------  
  55         |   2  --&gt; Here it is not getting the expected value.  
  66         |   11 --&gt; This is sum of Marks for Header_ID 66  
  </code></pre>
  
  <p>SUM function is CASE statement returns value 2 and I want set this to 0.  </p>
  
  <p>Can anyone please help me here?</p>