Add all the results of a table and separate by day / month

Question

Add all the results of a table and separate by day / month

Navigation

#1 by (3 votes)

3

I have a table, where I would like to add all the results and separate them by Day / Month.

01/Abril   =   3 Resultados;
03/Abril   =   5 Resultados;
02/Maio    =   1 Resultado;

The table I'm using.

**ID   --   item_data**
01   ||   2018-04-01
02   ||   2018-04-03
03   ||   2018-04-01
04   ||   2018-05-02
05   ||   2018-04-01
06   ||   2018-04-03
07   ||   2018-04-03
08   ||   2018-04-03
09   ||   2018-04-03

php mysql pdo

asked by anonymous 12.04.2018 / 03:31

1 answer

What is database redundancy? How do I display all 60 numbers

score 3 · Answer 1

You must use group by to group the dates equal:

select 
    count(*)                            as 'total',
    DATE_FORMAT(item_data,'%m/%d')      as 'data'    
from tabela
group by
DATE_FORMAT(item_data,'%m/%d');

EDIT

Example with timestamp

select 
    count(*)                                                    as 'total',
    DATE_FORMAT(FROM_UNIXTIME(item_data_timestamp), '%m/%d')    as 'data'    
from tabela
group by
    DATE_FORMAT(FROM_UNIXTIME(item_data_timestamp), '%m/%d')