mysql - Create view by performing multiple sums on different columns -

i have 2 tables in mysql database , need merge them in view. on both table must perfom math in order right result , must order them same day.

the first table 1 below , it's called chiusure:

+----+------------+--------+--------+---------+------+----+ | id | data       | totale | sconti | annulli | resi | sf | +----+------------+--------+--------+---------+------+----+ |  1 | 2016-03-01 | 153.82 |   1.07 |    0.00 | 0.00 | 34 | |  2 | 2016-03-02 | 241.58 |   0.01 |    0.00 | 0.00 | 32 | |  3 | 2016-03-03 |   0.00 |   0.01 |    0.00 | 0.00 |  0 | |  4 | 2016-03-04 |   0.00 |   0.00 |    0.00 | 0.00 |  0 | |  5 | 2016-03-05 |   0.00 |   0.00 |    0.00 | 0.00 |  0 | +----+------------+--------+--------+---------+------+----+ 

the second table 1 below , it's called emergenza:

+----+------------+----------+--------+ | id | data       | ora      | totale | +----+------------+----------+--------+ |  1 | 2016-03-04 | 09:30:00 |   2.20 | |  2 | 2016-03-04 | 09:40:00 |   9.00 | |  3 | 2016-03-04 | 09:50:00 |   5.00 | |....|............|..........|........| | 27 | 2016-03-05 | 09:14:00 |   4.40 | | 28 | 2016-03-05 | 09:27:00 |   5.00 | | 29 | 2016-03-05 | 09:33:00 |   2.20 | |....|............|..........|........| +----+------------+----------+--------+ 

what find difficult here in emergenza table there multiple row same date. in view (view_emergenza) grouped them date in way:

select     data,     sum(totale) chiusura,     count(id) sf  emergenza  group day(data); 

the result is:

+------------+----------+----+ | data       | chiusura | sf | +------------+----------+----+ | 2016-03-04 |   178.90 | 26 | | 2016-03-05 |   330.55 | 52 | | 2016-03-06 |   333.55 | 46 | | 2016-03-07 |   272.40 | 31 | | 2016-03-08 |   169.40 | 28 | | 2016-03-09 |   223.40 | 20 | | 2016-03-10 |   206.00 | 19 | | 2016-03-11 |   157.50 | 22 | +------------+----------+----+ 

moreover need perform math before summing 2 table. in view (view_chiusure) perfomed kind of math achieve wanted results:

select data, (totale - annulli - resi) chiusura, sf     chiusure 

the result is:

+------------+----------+----+ | data       | chiusura | sf | +------------+----------+----+ | 2016-03-01 |   153.82 | 34 | | 2016-03-02 |   241.58 | 32 | | 2016-03-03 |     0.00 |  0 | | 2016-03-04 |     0.00 |  0 | | 2016-03-05 |     0.00 |  0 | +------------+----------+----+ 

at point want merge 2 views in 1 unique view:

• group day(data) data
• (chiusure.totale - chiusure.annulli - chiusure.resi) + [sum(emergenza.totale) group emergenza.data emergenza.data = chiusure.data]
• chiusure.sf + [count(emergenza.id) group emergenza.data emergenza.data = chiusure.data]

update

i tried in way:

select         c.data,         c.chiusura + (select e.chiusura view_emergenza e e.data = c.data ) chiusura,         c.sf + (select e.sf view_emergenza e e.data = c.data ) sf     view_chiusure c 

but seems view_chiusure.chiusura , view_chiusure.sf totally ignored

+------------+----------+------+ | data       | chiusura | sf   | +------------+----------+------+ | 2016-03-01 |     null | null | | 2016-03-02 |     null | null | | 2016-03-03 |     null | null | | 2016-03-04 |   178.90 |   26 | | 2016-03-05 |   330.55 |   52 | | 2016-03-06 |   333.55 |   46 | | 2016-03-07 |   272.40 |   31 | | 2016-03-08 |   169.40 |   28 | | 2016-03-09 |   223.40 |   20 | | 2016-03-10 |   206.00 |   19 | +------------+----------+------+ 

the result should be

+------------+----------+------+ | data       | chiusura | sf   | +------------+----------+------+ | 2016-03-01 |   153.82 |   34 | | 2016-03-02 |   241.58 |   42 | | 2016-03-03 |     0.00 |    0 | | 2016-03-04 |   178.90 |   26 | | 2016-03-05 |   330.55 |   52 | | 2016-03-06 |   333.55 |   46 | | 2016-03-07 |   272.40 |   31 | | 2016-03-08 |   169.40 |   28 | | 2016-03-09 |   223.40 |   20 | | 2016-03-10 |   206.00 |   19 | +------------+----------+------+ 

is possible merge , sum 2 tables? if yes, how can it?

you must create views view_emergenza , view_chiusure @ first. must create view dates only:

create view vu_data   (   select data view_chiusure   union   select data view_emergenza   ) 

then create view distinct dates:

create view vu_distinct_data   select distinct data   vu_data 

now can create view join base views follows:

create view vu_join   select     (select c.chiusura view_chiusura c c.data=d.data)+     (select e.chiusura view_emergenza e e.data=d.data) chiusura,     (select c.sf view_chiusura c c.data=d.data)+     (select e.sf view_emergenza e e.data=d.data) sf,     d.data   vu_distinct_data d 

now can use vu_join:

select * vu_join order data