Difference of two series in MySQL

Question

I have a table with the following fields in MySQL: date, Value, Type.

Type can be either exports or imports. I want a query that returns the trade deficit (exports-imports) for each month. Is this possible with a single query?

Answer 1

I don't think you can do this in one query, because you need 2 different WHERE clauses.

You can retrieve the exports with the following query:

SELECT SUM(VALUE) FROM table WHERE MONTH(date) = month AND YEAR(date) = year AND Type = 'exports'

You can retrieve the imports with the following query:

SELECT SUM(VALUE) FROM table WHERE MONTH(date) = month AND YEAR(date) = year AND Type = 'imports'

This assumes that Value is a numeric field and date is a DATETIME or a DATE field.

Sorry for the SQL on one line, but I don't know how to line break in the code sample.

Answer 2

You should store your date as a year/month/day in separate columns, or have some way to easily link to a table that lets you do, otherwise you have to do slow calculations on every date column to work out the month. I'll leave it like you've got it for now, but I would suggest storing dates differently to make this calculation easier (if you only ever group by month, then an extra column that stores the result of EXTRACT(YEAR_MONTH FROM date) would make it much quicker overall.

Assuming "Value" is some kind of numeric:

SELECT
    EXTRACT(YEAR_MONTH FROM date) AS month,
    SUM(
        CASE Type
           WHEN 'export' THEN Value
           ELSE -1 * Value
        END
    ) AS trade_deficit
FROM
    mytable
GROUP BY 
    EXTRACT(YEAR_MONTH FROM date)

(I haven't tested this, and there may be some syntax mixups, but it should work)