MYSQL VIEW data listing

Question

I made a view that lists me a record count in Day, Week, Month and Year, but when I add new records the Day record doesn’t just change the others.

Follow the code of the day:

SELECT COUNT(id) FROM `agendas` WHERE funcionario_id = agendas.funcionario_id GROUP BY date_format(inicio, '%Y-%m-%d'), funcionario_id limit 1

follows all the codes together:

SELECT
    distinct
    (SELECT COUNT(id) FROM `agendas` WHERE funcionario_id = agendas.funcionario_id GROUP BY date_format(inicio, '%Y-%m-%d'), funcionario_id limit 1) as dia,
    (SELECT COUNT(id) FROM `agendas` WHERE  funcionario_id = agendas.funcionario_id and inicio BETWEEN DATE_ADD(now(), INTERVAL(1 - DAYOFWEEK(now())) DAY)  AND DATE_ADD(now(), INTERVAL(3 + DAYOFWEEK(now())) DAY) GROUP BY date_format(inicio, '%u') limit 1) as semana,
    (SELECT COUNT(id) FROM `agendas` WHERE funcionario_id = agendas.funcionario_id GROUP BY date_format(inicio, '%Y-%m'), funcionario_id limit 1) as mes,
    (SELECT COUNT(id) FROM `agendas` WHERE funcionario_id = agendas.funcionario_id GROUP BY date_format(inicio, '%Y'), funcionario_id limit 1) as ano,
    (select users.name from users,funcionarios where funcionarios.id = agendas.funcionario_id and users.id = funcionarios.user_id) as nome
from
    agendas

Why did I make more explanatory the return of the view

Return without days view only

Answer 1

You is not specifying the day you want to consult, your query for the day is always limited to the first day returned as a result, which in case is 2019-07-15. I have no way to test my proposed solution now, but I would advise you to change your day’s consultation to:

// Caso você queira a consulta do dia corrente
SELECT COUNT(id) FROM `agendas`
WHERE funcionario_id = agendas.funcionario_id
-- AQUI INDICO QUE QUERO SOMENTE OS RESULTADOS QUE SÃO IGUAIS A DATA ATUAL DO SISTEMA 
AND date_format(inicio, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
GROUP BY date_format(inicio, '%Y-%m-%d'), funcionario_id limit 1

Or else:

// Caso você queira a consulta do último dia que sem tem registro
SELECT COUNT(id) FROM `agendas`
WHERE funcionario_id = agendas.funcionario_id
GROUP BY date_format(inicio, '%Y-%m-%d'), funcionario_id
-- AQUI ORDENO OS RESULTADOS DE FORMA DECRESCENTE
ORDER BY date_format(inicio, '%Y-%m-%d') DESC
limit 1