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;