CODEWITHSUNDEEP
mysqlsql
User12345
User12345

Reputation: 325

MYSQL SUM & INSERT

I've been pulling my hairs apart to work out how to achieve the below.

I've created a SQL fiddle with the table structures & data at http://sqlfiddle.com/#!9/56e8d8/2/0

With reference to the fiddle, I'd like the table MASTER_ALL to look as below

 +-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
    | master_date | master_name | master_val1 | master_val2 | master_val3 | master_val4 | master_val5 | master_val6 |
    +-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
    | 2015-01-01  | a1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-01  | b1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-01  | c1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-01  | d1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-01  | e1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-01  | f1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | a1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | b1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | c1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | d1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | e1          |           0 |           1 |           2 |           3 |           4 |           5 |
    | 2015-01-02  | f1          |           0 |           1 |           2 |           3 |           4 |           5 |
     +-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

where,

 master_val1 = a_val1 + a_val2 + a_val3 from TABLE A1
 master_val2 = b_val1 + b_val2 + b_val3 + b_val4 + b_val5 from TABLE B1
 master_val3 = c_val1 + c_val2 + c_val3 + c_val4 from TABLE C1
 master_val4 = d_val1 + d_val2 from TABLE D1
 master_val5 = e_val1 from TABLE E1
 master_table6 = f_val1 from TABLE F1

the intention is to get add all the values as per the above requirement per day per user( I've referred to the users as *_name) in each of the tables.

Could someone please help me?

Copying the fiddle data here just in case the data gets purged after a while meaning, people wanting to refer to this solution would not have the sample data & thus the full picture of the study case.

CREATE TABLE MASTER_ALL
        (`master_date` date, `master_name` varchar(7), `master_val1` int, `master_val2` int, `master_val3` int, `master_val4` int, `master_val5` int)
;

CREATE TABLE A1
        (`a_date` date, `a_name` varchar(7), `a_val1` int, `a_val2` int, `a_val3` int)
;

CREATE TABLE B1
        (`b_date` date, `b_name` varchar(7), `b_val1` int, `b_val2` int, `b_val3` int,`b_val4` int, `b_val5` int)
;

CREATE TABLE C1
        (`c_date` date, `c_name` varchar(7), `c_val1` int, `c_val2` int, `c_val3` int, `c_val4` int)
;

CREATE TABLE D1
        (`d_date` date, `d_name` varchar(7), `d_val1` int, `d_val2` int)
;

CREATE TABLE E1
        (`e_date` date, `e_name` varchar(7), `e_val1` int)
;

CREATE TABLE F1
        (`f_date` date, `f_name` varchar(7), `f_val1` int)
;

INSERT INTO A1
        (`a_date`, `a_name`, `a_val1`, `a_val2`, `a_val3`)
VALUES
    ('2015-01-01', 'a1', 3,4,1),
    ('2015-01-01', 'b1', 2,3,5),
    ('2015-01-01', 'c1', 5,2,3),
    ('2015-01-02', 'a1', 3,2,5),
    ('2015-01-02', 'c1', 3,2,5)
;

INSERT INTO B1
        (`b_date`, `b_name`, `b_val1`, `b_val2`, `b_val3`, `b_val4`, `b_val5`)
VALUES
    ('2015-01-01', 'a1', 1,4,1,5,5),
    ('2015-01-01', 'b1', 3,3,5,4,6),
    ('2015-01-02', 'c1', 5,2,3,1,2),
    ('2015-01-03', 'a1', 3,2,5,3,2),
    ('2015-01-04', 'e1', 3,2,5,3,1)
;

INSERT INTO C1
        (`c_date`, `c_name`, `c_val1`, `c_val2`, `c_val3`, `c_val4`)
VALUES
    ('2015-01-01', 'e1', 4,4,1,5),
    ('2015-01-01', 'f1', 8,3,5,0),
    ('2015-01-04', 'c1', 5,2,3,1),
    ('2015-01-04', 'b1', 3,2,5,3),
    ('2015-01-05', 'e1', 3,2,5,3)
;

INSERT INTO D1
        (`d_date`, `d_name`, `d_val1`, `d_val2`)
VALUES
    ('2015-01-01', 'a1', 5,4),
    ('2015-01-01', 'z1', 6,3),
    ('2015-01-07', 'k1', 5,2),
    ('2015-01-05', 'b1', 3,2),
    ('2015-01-06', 'e1', 3,2)
;

INSERT INTO E1
        (`e_date`, `e_name`, `e_val1`)
VALUES
    ('2015-01-06', 'm1', 1),
    ('2015-01-10', 'e1', 4),
    ('2015-01-13', 'n1', 5),
    ('2015-01-12', 'p1', 3),
    ('2015-01-16', 'c1', 3)
;

INSERT INTO F1
        (`f_date`, `f_name`, `f_val1`)
VALUES
    ('2015-01-11', 'a1', 8),
    ('2015-01-04', 'e1', 2),
    ('2015-01-05', 'c1', 5),
    ('2015-01-23', 't1', 3),
    ('2015-01-17', 'y1', 3)
;

Upvotes: 1

Views: 166

Answers (1)

nick
nick

Reputation: 693

You can try to use the following query:

INSERT INTO master_all
SELECT date as master_date
      ,name as master_name
      ,SUM(m_val1) as master_val1
      ,SUM(m_val2) as master_val2
      ,SUM(m_val3) as master_val3
      ,SUM(m_val4) as master_val4
      ,SUM(m_val5) as master_val5
      ,SUM(m_val6) as master_val6
FROM (
select a_date as date
      ,a_name as name
      ,a_val1 + a_val2 + a_val3 as m_val1
      ,0 as m_val2
      ,0 as m_val3
      ,0 as m_val4
      ,0 as m_val5
      ,0 as m_val6
from a1
union all
select b_date as date
      ,b_name as name
      ,0 as m_val1
      ,b_val1 + b_val2 + b_val3 + b_val4 + b_val5 as m_val2
      ,0 as m_val3
      ,0 as m_val4
      ,0 as m_val5
      ,0 as m_val6
from b1
UNION ALL
select c_date as date
      ,c_name as name
      ,0 as m_val1
      ,0 as m_val2
      ,c_val1 + c_val2 + c_val3 + c_val4 as m_val3
      ,0 as m_val4
      ,0 as m_val5
      ,0 as m_val6
from C1
UNION ALL
SELECT d_date as date
      ,d_name as name
      ,0 as m_val1
      ,0 as m_val2
      ,0 as m_val3
      ,d_val1 + d_val2 as m_val4
      ,0 as m_val5
      ,0 as m_val6
from D1
UNION ALL
SELECT e_date as date
      ,e_name as name
      ,0 as m_val1
      ,0 as m_val2
      ,0 as m_val3
      ,0 as m_val4
      ,e_val1 as m_val5
      ,0 as m_val6
from E1
UNION ALL
SELECT f_date as date
      ,f_name as name
      ,0 as m_val1
      ,0 as m_val2
      ,0 as m_val3
      ,0 as m_val4
      ,0 as m_val5
      ,f_val1 as m_val6
from F1
) as t
GROUP BY date, name

Upvotes: 3

Related Questions