I have a column in TIMESTAMP and a column with a numeric value of type:
|---------------------|---------------------|
| fecha | valor |
|---------------------|---------------------|
| 2017-03-31 08:34:50 | 5 |
| 2017-03-27 08:34:50 | 4 |
| 2017-03-18 08:34:50 | 2 |
| 2017-03-27 08:34:50 | 8 |
|---------------------|---------------------|
I need to add SUM(valor)
when the date is a weekday of this week.
For example , we are currently on Friday the 31st, so the week starts on Monday the 27th. I have to add all the values that are in the last one, for example Monday, the 27th. I want to obtain the (4+8)
= 10. And so for all the days of the week later. I need something like the following:
SELECT SUM(valor) WHERE fecha = "el Lunes pasado";
I need that query to automatically recognize when the week starts and add the values for a selected day of the week (in the example it was Monday).
You can use the function
weekday
that returns a numerical index that represents which day of the week you are in with respect to the beginning of the week. In the case of a Friday day (such as2017-03-31
), the result is4
.You can then use that result and subtract that number of days from the current day to find the previous Monday:
Demo
So combining it with your query, it would be:
Or, if you want to avoid using the function
date()
with the columnfecha
in case you have defined an index on that column and want to use it for better performance, you can write the query a bit more complicated like this: