Sum all results of a table and separate by day/month

Asked

Viewed 53 times

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
  • 2

    You could put the table and the columns to help the "pessoar" to answer

1 answer

3

It is necessary to use the group by to group the same dates:

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')

  • It worked with the datetime format the way I wanted, but I can apply to the timestamp format?

  • Can yes, just convert to date and apply the date_format, I added an example to the answer

Browser other questions tagged php mysql pdo

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