MySQL : collect the sum of the associated values
<p>I have three tables in <code>database</code>:</p>

<p><strong>Table: article</strong></p>

<pre><code>id | code | name          | quantity | stock_date
--------------------------------------------------
1   1dfod   Article name    10        2016-04-01
</code></pre>

<p><strong>Table: selling</strong></p>

<pre><code>id | client_id | selling_type_id | selling_date | selling_status
----------------------------------------------------------------
1    1           1                 2016-04-02     1
2    1           1                 2016-04-03     1
3    1           1                 2016-04-04     1
</code></pre>

<p><strong>Table: selling_detail</strong></p>

<pre><code>id | selling_id | article_id | quantity
-------------------------------------
1    1            1            2
2    1            1            3
3    1            1            1
4    2            1            3
5    3            1            1
</code></pre>

<p>at the end I would have a <code>stock record</code> for this article like this:</p>

<pre><code>date      | in_stock (item in stock) | out_stock (sum of item sold)
----------------------------------------------------------------------
2016-04-01  10                          0
2016-04-02  0                           6
2016-04-03  0                           3
2016-04-04  0                           1
</code></pre>

<p>All <code>mysql</code> queries to my knowledge do not give me this result.</p>

<p>Here is my code:</p>

<pre><code>SELECT SUM(sd.quantity) out_stock, s.search_date, ifnull(ss.quantity, 0) in_stock
FROM selling_detail sd JOIN selling s ON (sd.selling_id = s.id)
LEFT JOIN shop_stock ss ON (ss.search_date = s.search_date) WHERE (sd.shop_stock_id = 1)
GROUP BY s.search_date;
</code></pre>