title: Sql Subquery with sum
body: <p>I have two tables Invoices and Payments. invoices have payments. I want to write a query that displays unpaid invoices and the remaining amount of the invoice, which is calculated by summing up the payments of the invoice and subtracting it from the invoice amount. I tried this query but it doesn't work. please how can i do it.</p>
  
  <pre><code>SELECT Invoice.[Invoice Date], Invoice.Item, Invoice.Quantity, 
      Invoice.[Unit Price], 
      Invoice.[Payment Status], Invoice.[LongDate], 
      Invoice.Quantity*Invoice.[Unit Price] - Sum(Q.Amount) AS Remaining
  FROM  
      (SELECT Invoice.[Invoice Id], [Payment ID]
         FROM Invoice 
         INNER JOIN Payment ON Invoice.[Invoice Id] = Payment.[Invoice Id]) AS Q
      INNER JOIN Invoice ON Q.[Invoice Id] = Invoice.[Invoice Id]
  GROUP BY Invoice.[Invoice Id]; 
  </code></pre>