SELECT date and then putting it into group

Question

I am trying to select dates from my DB and then group it as months and years for example:

May 2019
June 2018
etc.

DB -the date is type date

I have this code:

  SELECT datum, count(*) FROM zapasy GROUP BY datum

Which makes it on each day, but I don't want that so I searched how to make it group as months and years, not just days

 SELECT datum, count(*) FROM zapasy GROUP BY MONTH(datum), YEAR(datum)

and I came up with this, however, I am getting this stupid error

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zapasy_db_test.zapasy.datum' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

And I have no idea what is wrong with the code

Please, give me some advice. Thank you

It's because of `sql_mode=only_full_group_by`; Maybe you can try like this `SELECT MONTH(datum), YEAR(datum), count(*) FROM zapasy GROUP BY MONTH(datum), YEAR(datum)` ? — FanoFN, May 22 '19 at 08:40
Possible duplicate of [MySQL : isn't in GROUP BY](https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by) — Topher, May 22 '19 at 08:45

score 1 · Answer 1 · answered May 22 '19 at 08:42

You need to have the items you're grouping by in your select.

So

 SELECT MONTH(datum), YEAR(datum), count(*) from ...  group by MONTH(datum), YEAR(datum)

instead of datum, otherwise a date column you're selecting, will have a day in it, which you'd get from just datum, and that has nothing to do with the count and group and would be wrong, or the grouping wouldn't work at all.

score 0 · Accepted Answer · answered May 22 '19 at 08:51

I notice that your desired output is in this format May 2019 etc. You know that DATE datatype is like this, yyyy-mm-dd so if you really, really want to extract the month-year in that format, you can try the following:

SELECT DATE_FORMAT(datum, '%b %Y') AS 'Monthyear', COUNT(*) 
FROM zapasy 
GROUP BY Monthyear;

SELECT date and then putting it into group

2 Answers2