-1

I'm have trouble with having to work with ONLY_FULL_GROUP_BY mode (I wouldn't want to, but I'm forced to). The problem is that this requires me to include a column in the group by clause. This is a column I need to select (it's a timestamp column), but need to group by "day, month, and year". like so:

SELECT COUNT(id) AS howmuch, date_event 
FROM mytable AS st 
WHERE date_event BETWEEN SUBDATE(CURDATE(), 7) AND NOW() 
GROUP BY DAY(date_event), MONTH(date_event), YEAR(date_event) 
ORDER BY date_event

but this throws error: 

'mydb.st.date_event' isn't in GROUP BY

because date_event is not in GROUP BY, because it can't be. If I add date_event to group by, the query works, but logically it does not return what I want (I need to know how many entries are in that DB table, grouped by day.

Any hints how to achieve this?

Thanks!!

germi
  • 95
  • 5
  • If you are grouping the data by Day, Month, Year, Shouldn't you select that data in that manner also? – Ankit Bajpai Jul 10 '18 at 11:40
  • Better if you add example data place it on sqlfiddle.com or db-fiddle.com. Also provide expected output as ascii data table (text formatted) based on the example data – Raymond Nijland Jul 10 '18 at 11:46

1 Answers1

1

A day by definition is not some free standing abstraction, but also lies in a month and a year. So, you can just select the day, month, and year in your query:

SELECT
    DAY(date_event),
    MONTH(date_event),
    YEAR(date_event),
    COUNT(id) AS howmuch
FROM mytable
WHERE date_event BETWEEN SUBDATE(CURDATE(), 7) AND NOW() 
GROUP BY DAY(date_event), MONTH(date_event), YEAR(date_event) 
ORDER BY YEAR(date_event), MONTH(date_event), DAY(date_event);

But we can make this more concise by just grouping by the DATE(date_event):

SELECT
    DATE(date_event),
    COUNT(id) AS howmuch
FROM mytable
WHERE date_event BETWEEN SUBDATE(CURDATE(), 7) AND NOW() 
GROUP BY DATE(date_event)
ORDER BY DATE(date_event);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • yep, that seems to do the trick..! many thanks! – germi Jul 10 '18 at 11:51
  • just a quick follow up... I need to build a very similar query, but in this case the group by is not "by DATE", but "by month". and also the same query, to group just by year. is there an equivalent to "DATE()" but for month? – germi Jul 10 '18 at 11:52
  • @germi you really should start using a search engine... https://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql – Shadow Jul 10 '18 at 11:57
  • @germi Either use `GROUP BY DATE_FORMAT(date_event,'%Y-%m')` or else just group by the month and year separately `GROUP BY YEAR(date_event), MONTH(date_event)`. – Tim Biegeleisen Jul 10 '18 at 11:58