Schema:
CREATE TABLE purchases (name varchar(20), units SMALLINT, ts int);
INSERT INTO purchases (name, units, ts)
VALUES
('John', 1, UNIX_TIMESTAMP('2017-01-03 12:00:00')),
('John', 1, UNIX_TIMESTAMP('2017-01-04 12:00:00')),
('John', 1, UNIX_TIMESTAMP('2018-01-01 12:00:00')),
('John', 1, UNIX_TIMESTAMP('2018-01-01 12:00:00')),
('Jill', 1, UNIX_TIMESTAMP('2018-01-01 12:01:00')),
('Jill', 1, UNIX_TIMESTAMP('2018-01-01 12:01:00')),
('Jill', 1, UNIX_TIMESTAMP('2016-02-20 12:01:00')),
('Jill', 1, UNIX_TIMESTAMP('2016-03-13 12:01:00')),
('Jack', 1, UNIX_TIMESTAMP('2002-12-08 12:02:00')),
('Jean', 1, UNIX_TIMESTAMP('2005-12-11 12:03:00'));
Current SQL query uses 2 passes:
SELECT * FROM
(SELECT
name, SUM(units) as histPurchases
FROM purchases
WHERE ts
BETWEEN UNIX_TIMESTAMP('1990-01-01 00:00:00')
AND UNIX_TIMESTAMP('2019-02-01 00:00:00')
GROUP BY name) as hist
JOIN
(SELECT name, SUM(units) as currPurchases # those bought on the 1st of Jan 2018
FROM purchases
WHERE ts
BETWEEN UNIX_TIMESTAMP('2018-01-01 00:00:00')
AND UNIX_TIMESTAMP('2018-01-02 00:00:00')
GROUP BY name) as curr
ON (hist.name = curr.name);
Here's the result:
name | histPurchases | currPurchases
--------|---------------|------------
Jill | 4 | 2
John | 4 | 2
Is there a way to structure the query so that only one pass is made (ideally with the latest version of MySQL)? Here is the SQL fiddle.