title: MYSQL Create or pick a Date into group, but unable to display correct amount
body: <p>I having a bit blocking issue on code, I am able to get total amount but unable to get amount in the order, which i am stuck on it and need your advice.</p>
  
  <p>Here is a Screenshot a table of SQL data.</p>
  
  <p><a href="https://i.stack.imgur.com/LoZXt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LoZXt.png" alt="enter image description here"></a></p>
  
  <p>Here is a code that I  wrote </p>
  
  <pre><code>SELECT 
      settlement_start_date,
      settlement_end_date,
      SUM(total_amount) AS total_Amount,
      SUM(IF(transaction_type = 'order',
          amount,
          0)) AS 'Order'
  FROM
      settlements
  WHERE
      (settlement_end_date BETWEEN '2017-08-30' AND '2017-09-10')
  </code></pre>
  
  <p>the problem is 0000-00-00 date keep blocking me if I  type date on 29/08/2017 to 10/09/2017.</p>
  
  <p>They will show an only Total amount on the first line but won't pick up their rest of amount, they are showing only 0.00. I have tried Settlement_id as a group but fail. is there another way to allow group as Settlement ID together as a total with a date?</p>
  
  <p><a href="https://i.stack.imgur.com/CjT01.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CjT01.png" alt="enter image description here"></a></p>