Let’s say I want to get the profit between two dates. Then I can do something like this:
SELECT SUM(Profit) FROM Sales WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters>
I would then like to compare it to a previous period offset by a fixed amount. It could be written something like this to get it in two rows:
SELECT SUM(Profit) FROM Sales WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters> UNION ALL SELECT SUM(Profit) FROM Sales WHERE date BETWEEN '2014-01-01' - INTERVAL 1 YEAR AND '2014-02-01' - INTERVAL 1 YEAR AND <other_filters>
Is there a way to do this without a union? I am looking for something like this:
SELECT SELECT SUM(Profit), ??? FROM Sales WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters>
I think the tricky part here is how to ‘un-do’ the where filter for the offseted-time calculation.
Advertisement
Answer
You can use conditional aggregation and OR
the range checks in the WHERE
clause (unless they are subsequent in which case you can combine them directly of course).
SELECT sum(CASE WHEN date >= '2014-01-01' AND date < '2014-02-02' THEN profit ELSE 0 END), sum(CASE WHEN date >= '2014-01-01' - INTERVAL 1 YEAR AND date < '2014-02-02' - INTERVAL 1 YEAR THEN profit ELSE 0 END) FROM sales WHERE date >= '2014-01-01' AND date < '2014-02-02' OR date >= '2014-01-01' - INTERVAL 1 YEAR AND date < '2014-02-02' - INTERVAL 1 YEAR;
Note: Prefer not to use BETWEEN
here but check for a right half open range check. That way, if the precision of date
changes, records on the end past midnight are still in the results.