How to make a weekly mysql query

Question

Good morning everyone! I would like a Help!!!

I am in doubt of how to make a weekly mysql query, starting on Monday and ending on Sunday, and the data will be inserted every day, and from the data is inserted adding up a value and when it is on Monday it starts the sum again, and it cannot add up to the previous week

example of the query I made but am not succeeding:

SELECT id, data, valor 
FROM pagamento 
WHERE WEEK(data ,7) = WEEK(NOW())
  AND reftype = 'credito'
  AND status != 99

Answer 1

You can create a function to calculate the first day of the week:

CREATE FUNCTION PRIMEIRO_DIA_DA_SEMANA(dia DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN SUBDATE(dia, WEEKDAY(dia));
END

And then use to pick up the values between this date:

SELECT p.id,
       p.data,
       p.valor
FROM pagamento
WHERE reftype = 'credito'
  AND status <> 99
  AND p.data >= PRIMEIRO_DIA_DA_SEMANA(NOW())
  AND p.data < PRIMEIRO_DIA_DA_SEMANA(DATE_ADD(NOW(),INTERVAL 1 WEEK))

And to get the total you just need the SUM of value:

SELECT SUM(p.valor) AS total
FROM pagamento
WHERE reftype = 'credito'
  AND status <> 99
  AND p.data >= PRIMEIRO_DIA_DA_SEMANA(NOW())
  AND p.data < PRIMEIRO_DIA_DA_SEMANA(DATE_ADD(NOW(), INTERVAL 1 WEEK))

---

Reference: How do I get the first day of the week of a date in mysql?