title: Getting total for Invoiced Paid and the amount owing for each client
body: <p>I have tried many of the scripts that I found here but none did what I required. </p>
  
  <p>I have 3 tables: <code>clients</code>, <code>invoiced</code> and <code>payments</code>. </p>
  
  <p>I would like to:</p>
  
  <ul>
  <li>sum all invoices per client within a date range</li>
  <li>sum all payments for clients with in the date range then subtract payments from invoiced to show amount outstanding. </li>
  </ul>
  
  <p>I need to add the date range and get the total if not zero.</p>
  
  <p>So far my query is</p>
  
  <pre><code>select id,
     (select sum(Total) from invoiced  where invoiced.ClientId = clients.Id) AS Invoiced,
     (select sum(Amount) from payments  where payments.ClientId = clients.Id) AS Paid
  FROM clients
  </code></pre>