jeudi 4 octobre 2018

MySQl: How to groupby ('year') a column with date format, ignoring month and day

I am building an app with Laravel. I am using Eloquent and MySql Database. In my MySQL database I have a table EVENTS like this:

id  EVENT   YEAR
1   event1  2000-05-27
2   event2  2018-01-01
3   event3  2000-02-12
4   event4  2000-06-03

This example is just for simplicity, obviously my database has hundreds of events ocurring at any time.

The data in the column year has the 'date' format

I need to get from this MySQL table a resume of events ocurred per year. In the example would be

year    number of events
2000    3
2018    1

I did this:

$years = Event::groupBy('year')
                    ->selectRaw('count(*) as total, year')
                    ->get();

and I get this:

year    number of events
2000    1
2018    1
2000    1
2000    1

Because each date in the column is different (In the example table: 2000-02-12 !== 2000-06-03 !== 2000-05-27).

I need to group together all events happening in each year together without taking into account mont and day.

The question is:

How do I group by colum 'year' but taking into account just the year even if the events ocurred in different days and months?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire