1

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

0
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`
0

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
  • No scalar value - no sum, so LEFT join and IFNULL are excess I think... – Akina Sep 20 at 12:03
  • Yes you are correct. Thank you for the remark. – PhilippD Sep 20 at 12:22
  • There is one more reason why IFNULL is excess. With it according multiplying result is 0 and do not affect on the sum. Without it (simple SUM(table1.scal_val * table2.state)) according multiplying result is NULL, but such aggregate functions as SUM, COUNT, etc. ignores/skippes NULL values, so it do not affect on the sum again. – Akina Sep 20 at 12:35
  • Nevertheless your query may make sense - in the case when the author needs to obtain all dates listed in table2, even if no IDs with according scal_val for that date. But in most cases it is more safe to generate according dates list within the range using recursive CTE or another technique. – Akina Sep 20 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.