1

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;
1

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;
  • Thanks for taking the trouble of posting the solution as an answer. Too bad Akina shied away from doing so himself :) On a different note, as far as I can see (maybe I'm missing something, not sure), your original query would be semantically equivalent to (and therefore producing the same results as) this if you replaced the left joins with inner joins and UNION with UNION ALL. (Depending on the nature of the data in the amount column in both invoices and bills, UNION might even work the same as UNION ALL in this specific case in terms of the results produced.) – Andriy M May 27 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.