Join 3 tables, aggregate over date field and subtract sum of two columns I have 3 tables and the they have the following columns.

shipments: shipment_id (PK), shipment_date (date)

invoices: invoice_id (PK), shipment_id (FK), amount (float)

bills: bill_id (PK), shipment_id (FK), amount (float)


Each shipment may have multiple invoices and bills. The net revenue of a month is defined as the sum of all bill amounts subtracted from the sum of invoice amounts in that month (derived from shipment_date). While a whole shipment may span across multiple months, we're only concerned about net revenue of the month as an aggregate.

Is there a way to calculate net revenues of all the months (depending on the data) with a single SELECT query?

Ignore the database design. And I'm trying the following query.

SELECT
    u.`month`  AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        i.`amount` AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `invoices` i
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = i.`shipment_id`
    UNION
    SELECT
        (-1 * b.`amount`) AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `bills` b
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = b.`shipment_id`) u
GROUP BY u.`month`
ORDER BY u.`month` ASC;
This is the final query that worked, with help from Akina's comments.

SELECT
    DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        `amount`,
        `shipment_id`
    FROM
        `invoices`
    UNION ALL
    SELECT
        (-1 * `amount`) AS `amount`,
        `shipment_id`
    FROM
        `bills`) u
    JOIN `shipments` s
        ON u.`shipment_id` = s.`shipment_id`
GROUP BY `month`
ORDER BY `month` ASC;