Complex joins with SUM

I am going to describe this problem as best I can. Hopefully someone can make sense of what I'm dealing with and can help me get out of this mess. This is a multiline, relatively complex query involving a lot of tables, that was thrown onto my plate. Here is a simplified SQL Fiddle showing the table and data structure: http://sqlfiddle.com/#!9/721ea5/1/0

I will try to explain the data structure here.

• post_type_trust: Contains only a Trust ID and a Trust Name

• post_type_line_item: Contains only a Line Item ID and a Line Item Name

• post_type_ar_invoice: Contains an Invoice ID, Invoice Status, and Invoice Date

• invoice_line_item: This table ties together the post_type_ar_invoice table and the post_type_line_item table. Basically an invoice can contain many line items. This table represents that relationship.

• trust_line_item_pivot_table: This table ties together the post_type_trust and post_type_line_item tables. Basically, a line item can belong to many trusts.

• post_type_ar_payment: This table stores all of the payments that have been made for a post_type_ar_invoice invoice.

• payment_parts: An invoice can contain many individual items. When a payment is made to an invoice, that payment will be split among the various items in that invoice. This table stores that data.

In the SQL Fiddle, you can see that the existing code is already successfully pulling quite a bit of data where the INVOICE DATE is between two dates. I need to add one more column to this output. For each row in the output (Each row represents a line item from an invoice), I need to also SUM the payments that were made for that line item between these same two dates. It's important to note here that for this column, we only care about the payments that were actually made between the two dates. So if an invoice line item is in the output (Because the invoice was created between the two dates) but the line item didn't receive any payments during tha

Assuming you just want to add a calculated column showing the aggregated payment for each line item that was made within a date range, you could implement it in the form of a correlated subquery:

SELECT
  ... /* all your current columns */
, (
    SELECT
      SUM(pp.amount)
    FROM
      payment_parts AS pp
      INNER JOIN post_type_ar_payment AS ptap
        ON pp.payment_id = ptap.id AND pp.invoice_id = ptap.invoice_id
    WHERE
      ptap.payment_date BETWEEN '2018-08-01' AND '2018-08-31'
      AND pp.invoice_line_item_id = invoice_line_item.id
  ) AS payment
FROM
  ... /* the rest of the query */
;

Demo at SQL Fiddle: http://sqlfiddle.com/#!9/721ea5/3

StackExchange Database Administrators Q#214815, answer score: 4