CODEWITHSUNDEEP
mysqlsql
nekiala
nekiala

Reputation: 480

MySQL : collect the sum of the associated values

I have three tables in database:

Table: article

id | code | name          | quantity | stock_date
--------------------------------------------------
1   1dfod   Article name    10        2016-04-01

Table: selling

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

Table: selling_detail

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

at the end I would have a stock record for this article like this:

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

All mysql queries to my knowledge do not give me this result.

Here is my 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;

Upvotes: 1

Views: 64

Answers (2)

Strawberry
Strawberry

Reputation: 33945

SELECT date,SUM(in_stock) in_stock,SUM(out_stock) out_stock FROM
 (
 SELECT stock_date date,quantity in_stock,0 out_stock FROM article
  UNION
 SELECT selling_date,0,quantity FROM selling JOIN selling_detail ON selling_detail.selling_id = selling.id
 ) x
GROUP BY date;

Upvotes: 1

Arth
Arth

Reputation: 13110

As you are trying to combine similar data from two very different tables, you'll probably be staring down the barrel of a UNION ALL.

Something along these lines should get you started:

     SELECT * 
       FROM (
         SELECT a.stock_date `date`, 
                SUM(a.quantity) `in_stock (item in stock)`,
                0 `out_stock (sum of item sold)`
           FROM article a
          WHERE a.id = :article_id
       GROUP BY `date`          
      UNION ALL
         SELECT s.selling_date,
                0,    
                SUM(sd.quantity)
           FROM selling s
           JOIN selling_detail sd
             ON sd.selling_id = s.id
            AND sd.article_id = :article_id
       /* WHERE s.selling_type = ??
            AND s.selling_status = ?? /* If necessary */
        GROUP BY `date`          
             ) sr
    ORDER BY `date`

Upvotes: 0

Related Questions