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

Asked

Viewed 1,896 times

1

I need to make a query, to return the number of records registered in EVERY DAY, between two dates.

I’m using a mysql database, and I tried here, but no way seemed right.

mysql sql query

  • What format is the date in DB?

  • Date is in DATE format.

2 answers

3


SELECT count(*), DAYOFYEAR(colunaData) as diaDoAno
FROM tabela
WHERE colunaData BETWEEN dia and dia
GROUP BY diaDoAno

2

Just group per 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 single string 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
;

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.