How to do a record count for each day between one date and another?

1

I need to make an appointment, to return the number of registrations registered in EVERY DAY, between two dates.

I'm using a MYSQL database, and I tried it here, but no form seemed to me correct.

    
asked by anonymous 14.06.2016 / 16:45

2 answers

3
SELECT count(*), DAYOFYEAR(colunaData) as diaDoAno
FROM tabela
WHERE colunaData BETWEEN dia and dia
GROUP BY diaDoAno
    
14.06.2016 / 17:06
2

Just group by day using day(data) :

select
    day(t.data) as 'Dia',
    count(*) as 'Quantidade'
from
    tabela t
where
    t.data between '2016-06-01' and '2016-06-14'
group by 
    Dia
;

If you need the full date, I recommend using date_format to have day / month / year in a string only and group by it:

select
    date_format(t.data, '%d/%m/%Y') as 'Dia',
    count(*) as 'Quantidade'
from
    tabela t
where
    t.data between '2016-01-01' and '2016-06-14'
group by 
    Dia
;
    
14.06.2016 / 17:06