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
Best Answer