List all articles with their amounts at a certain date+time I've got a table with articles, and their stock amount changes:

TABLE stock_history:
+------------+---------------------+--------+
| article_id | amount_at           | amount |
+------------+---------------------+--------+
|       0001 | 2016-01-19 08:00:00 |    100 |
|       0001 | 2016-02-20 08:00:00 |    500 |
|       0001 | 2016-03-21 08:00:00 |      0 |
|       0002 | 2016-01-17 08:00:00 |    999 |
|       0002 | 2016-02-23 08:00:00 |    500 |
|       0002 | 2016-04-21 08:00:00 |   5500 |
|       0003 | 2016-02-19 08:00:00 |   1000 |
|       0003 | 2016-03-03 08:00:00 |    500 |
|       0003 | 2016-03-21 08:00:00 |  -1500 |
+------------+---------------------+--------+


Now I need a query which lists all articles with their amounts at a certain date+time, for example 2016-03-01: 

I will need to use subqueries like here:
http://dev.mysql.com/doc/refman/5.7/en/example-maximum-row.html

But I will need to use there SQL expressions somehow:


amount_at &lt; '2016-03-01 00:00:00'
MAX(amount_at)
GROUP BY article_id


The expected output should be like this:

+------------+---------------------+--------+
|       0001 | 2016-02-20 08:00:00 |    500 |
|       0002 | 2016-02-23 08:00:00 |    500 |
|       0003 | 2016-02-19 08:00:00 |   1000 |
+------------+---------------------+--------+


note I will need to join other tables, too, after, but that's another problem. :)

It's a little bit difficult...
I you don't care about the time in the result it's pretty easy:

select article_id, sum(amount)
from stock_history 
where amount_at &lt;= '2016-03-01 00:00:00'
group by article_id


If you want time, you need to determine which time you should present. If you want the same time as in the where clause you can just add it since it is a constant:

select article_id, '2016-03-01 00:00:00', sum(amount)
from stock_history 
where amount_at &lt;= '2016-03-01 00:00:00'
group by article_id


Assuming it is the last time before this time you can map all times to this time with a derived table and use that:

select a.article_id, b.amount_at, sum(a.amount)
from stock_history a
join ( select article_id, max(amount_at) as amount_at 
       from stock_history 
       where amount_at &lt;= '2016-03-01 00:00:00'
       group by article_id
) as b
    on a.rticle_id = b.article_id
   and a.amount_at &lt;= b.amount_at
group by a.article_id, b.amount_at;