Aggregate results from select queries

Question

I have a table with three columns: id, date, state.

For each unique value of id, I would like to select date and state, multiply the state array by a scalar variable for each id defined from else, and then sum up the state arrays where the date column is the same value.

How would this be achieved in a mysql statement?

Example:

SELECT scal_val FROM table1 WHERE id = 413 into @scal_val;
SELECT date, state * @scal_val as a FROM table2 WHERE id = 413
# now need to loop through ids in table2 for each id, adding state*@scal_val each time

table1:

╭───╥────────────┬─────────────╮
│   ║     id     │  scal_val   │
╞═══╬════════════╪═════════════╡
│ A ║ 413        │ 250         │
│ B ║ 414        │ 50          │
│ C ║ 415        │ 10          │
└───╨────────────┴─────────────┘

table2:

╭───╥────────────┬─────────────┬─────────────╮
│   ║     id     │    date     │    state    │
╞═══╬════════════╪═════════════╪═════════════╡
│ A ║ 413        │ 2016-01-01  │       1     │
│ B ║ 413        │ 2016-01-02  │       0     │
│ C ║ 413        │ 2016-01-03  │       1     │
│ D ║ 414        │ 2016-01-01  │       1     │
│ E ║ 414        │ 2016-01-02  │       1     │
│ F ║ 414        │ 2016-01-03  │       1     │
│ G ║ 415        │ 2016-01-01  │       1     │
│ H ║ 415        │ 2016-01-02  │       0     │
│ I ║ 415        │ 2016-01-03  │       0     │
└───╨────────────┴─────────────┴─────────────┘

result:

╭───╥────────────┬─────────────╮
│   ║   date     │    state    │
╞═══╬════════════╪═════════════╡
│ A ║ 2016-01-01 │ 310         │
│ B ║ 2016-01-01 │ 50          │
│ C ║ 2016-01-01 │ 300         │ 
└───╨────────────┴─────────────┘

MySQL version 5.6.10

Answer 1

SELECT t2.`date`, SUM(t1.scal_val * t2.state) state
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
GROUP BY t2.`date`

Answer 2

I think what you want is the following.

SELECT table2.date, SUM(IFNULL(table1.scal_val,0) * table2.state) 
FROM table2
LEFT JOIN table1
ON table1.id = table2.id
GROUP BY table2.date