CODEWITHSUNDEEP
mysqlsql
Surya Rawat
Surya Rawat

Reputation: 111

How To Merge Data From Two Tables

I have two tables, L1 and L2 and i want to merge data of these two tables, I want to fetch sum of sale as per item_id according to dates,I want to fetch data from 2012-01-20 to 22-01-2012,L1 table is containing data from 2012-01-20 to 2012-01-21 and L2 tables is containing data of 2012-01-22. please help me out.

Table L1

+-----------------+-------+---------
| date            | sale  | item_id |
+-----------------+-------+---------+
|2012-01-20       | 20    |A        | 
|2012-01-20       | 10    |B        |
|2012-01-21       | 20    |A        |
|2012-01-21       | 30    |B        |
|                 |       |         |
+----+-------------+-----------------

Table L2

+-----------------+-------+----------
| date            | sale  | item_id |
+-----------------+-------+---------+
|2012-01-22       | 20    |A        | 
|2012-01-22       | 10    |B        |     
+----+-------------+-----------------

wanted result

+-------+----------
| sale  | item_id |
+-------+---------+
| 60    |A        | 
| 50    |B        |
+------------------

Upvotes: 0

Views: 83

Answers (2)

ArKano
ArKano

Reputation: 58

maybe the error refers to the inner tables ... should the query be as follows?:

select item_id, sum(sale) as sale from ((select date, sale, item_id from l1 ) ALIAS1 union all (select date, sale, item_id from l2 ) ALIAS2 ) t group by item_id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can do this with a union all before the aggregation:

select item_id, sum(sale) as sale
from ((select date, sale, item_id
       from l1
      ) union all
      (select date, sale, item_id
       from l2
      )
     ) t
group by item_id;

You can add a where clause for the date range you want.

Upvotes: 5

Related Questions