MySQL – Complex Joins with SUM Function

MySQL

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 that time, then the SUM should be 0.

No matter what I try, I just cannot get this to output the data that I need. I did not write the existing code or DDL and I'm having a hard time modifying it to do what I need.

DDL:

CREATE TABLE `post_type_trust` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `Trust_Name` varchar(255) NOT NULL DEFAULT '',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `post_type_line_item` (
  `id` int(11) NOT NULL DEFAULT '0',
  `Name` varchar(255) NOT NULL DEFAULT '',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `invoice_line_item` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `line_item_id` int(11) DEFAULT '0',
  `description` varchar(255) DEFAULT '',
  `total` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `line_item_id` (`line_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1519938 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `post_type_ar_invoice` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `invoice_status` varchar(255) NOT NULL DEFAULT 'Estimate',
  `invoice_date` date DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `trust_line_item_pivot_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `trust_id` int(11) NOT NULL DEFAULT '0',
  `line_item_id` int(11) NOT NULL DEFAULT '0',
  `trust_percentage` decimal(15,2) NOT NULL DEFAULT '0.00',
  `trust_flat` decimal(15,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;


CREATE TABLE `post_type_ar_payment` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `invoice_id` int(11) unsigned NOT NULL,
  `payment_date` date NOT NULL,
 UNIQUE KEY `id` (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `payment_date` (`payment_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;


CREATE TABLE `payment_parts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) unsigned NOT NULL,
  `line_item_id` int(11) unsigned NOT NULL,
  `invoice_line_item_id` int(11) unsigned NOT NULL,
  `payment_id` int(11) unsigned NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `payment_id` (`payment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4;

INSERT INTO `post_type_trust` (`id`, `Trust_Name`)
VALUES
    (1037433, 'Single Item');

INSERT INTO `post_type_line_item` (`id`, `Name`)
VALUES
    (1037434, 'Single Item');

INSERT INTO `invoice_line_item` (`id`, `invoice_id`, `line_item_id`, `description`, `total`)
VALUES
    (1519937, 1037435, 1037434, 'Item 2', 200.00),
    (1519936, 1037435, 1037434, 'Item 1', 200.00);

INSERT INTO `post_type_ar_invoice` (`id`, `invoice_status`, `invoice_date`)
VALUES
    (1037435, 'Invoice', '2018-08-13');


INSERT INTO `trust_line_item_pivot_table` (`id`, `trust_id`, `line_item_id`, `trust_percentage`, `trust_flat`)
VALUES
    (5, 1037433, 1037434, 10.00, 25.00);

INSERT INTO `payment_parts` (`id`, `invoice_id`, `line_item_id`, `invoice_line_item_id`, `payment_id`, `amount`)
VALUES
    (41, 1037435, 1037434, 1519936, 1037436, 50.00);

INSERT INTO `post_type_ar_payment` (`id`, `invoice_id`, `payment_date`)
VALUES
    (1037436, 1037435, '2018-08-13');

Query:

SELECT 
    post_type_trust.id,
    post_type_line_item.Name AS item,
    invoice_line_item.invoice_id,
    invoice_line_item.id as invoice_line_item_id,
    post_type_ar_invoice.invoice_date,
    trust_line_item_pivot_table.trust_percentage AS trusted_percentage,
    trust_line_item_pivot_table.trust_flat AS flat,
    invoice_line_item.total,
    invoice_line_item.description,
    (trust_line_item_pivot_table.trust_percentage / 100) * invoice_line_item.total AS calculated_total
FROM  post_type_trust AS post_type_trust
LEFT JOIN trust_line_item_pivot_table AS trust_line_item_pivot_table ON post_type_trust.id = trust_line_item_pivot_table.trust_id
LEFT JOIN invoice_line_item AS invoice_line_item ON trust_line_item_pivot_table.line_item_id = invoice_line_item.line_item_id
LEFT JOIN post_type_ar_invoice AS post_type_ar_invoice ON invoice_line_item.invoice_id = post_type_ar_invoice.id AND `invoice_status` <> 'Estimate'
LEFT JOIN post_type_line_item AS post_type_line_item ON invoice_line_item.line_item_id = post_type_line_item.id

WHERE 
    post_type_trust.id = 1037433 AND 
    post_type_ar_invoice.invoice_date >= '2018-08-01' AND 
    post_type_ar_invoice.invoice_date <= '2018-08-31'
ORDER BY post_type_ar_invoice.invoice_date ASC

Best Answer

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

Related Question