Jay Chakra Jay Chakra - 3 years ago 145
MySQL Question

MySql aggregate by different initial values on other column

I have a table structure as follows

| user_id | value | date |
|---------|-------|------------|
| 1 | 5 | 2017-09-01 |
| 2 | 6 | 2017-09-01 |
| 1 | 1 | 2017-09-02 |
| 1 | 2 | 2017-09-03 |
| 2 | 9 | 2017-09-02 |
| 1 | 3 | 2017-09-04 |
| 2 | 5 | 2017-09-04 |
| 2 | 5 | 2017-09-05 |
| 1 | 1 | 2017-09-05 |
| 1 | 5 | 2017-09-06 |
| 1 | 6 | 2017-09-07 |
| 1 | 3 | 2017-09-08 |
| 1 | 4 | 2017-09-09 |
| 2 | 6 | 2017-09-06 |
| 1 | 1 | 2017-09-10 |


I have another table where initial cutoff date for user_ids are given like

| user_id | date |
|---------|------------|
| 1 | 2017-09-04 |
| 2 | 2017-09-05 |


The final cutoff date for all the user is 2017-09-08

I want to get the sum of values aggregated by user_id

What I have tried is

SELECT user_id, SUM(value) as Total
FROM table
WHERE date >= $DATE and date <= '2017-09-08'
GROUP BY user_id


I am stuck how should I deal with $DATE as it is variable for each user

The solution in the case should be

| user_id | Total |
|---------|------------|
| 1 | 18 |
| 2 | 11 |

Answer Source

Say you have table names as users and cutoff. cutoff having cutoff date for each user. Try this and let me know if it works or not.

select u.user_id,sum(u.value) Total 
from users u join cutoff c on u.user_id=c.user_id and (u.date>=c.date and u.date<='2017-09-08') 
group by u.user_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download