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;
Best Answer
This is the final query that worked, with help from Akina's comments.