Group by Month and Year in MySQL

Group by month and year in MySQL

GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);

is what you want.

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

How to Group By Year and Month in MySQL

This query will count all the rows, and will also count just the rows where Attribute is not null, grouping by year and month in rows:

SELECT
  Year(`date`),
  Month(`date`),
  Count(*) As Total_Rows,
  Count(`Attribute`) As Rows_With_Attribute
FROM your_table
GROUP BY Year(`date`), Month(`date`)

(this because Count(*) counts all the rows, Count(Attibute) counts all the rows where Attribute is not null)

If you need your table in PIVOT, you can use this to count only the rows where Attribute is not null:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then `Attribute` end) As Jan,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

And this to count all the rows:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan,
  Count(case when month(`date`)=2 then id end) As Feb,
  Count(case when month(`date`)=3 then id end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

(or, instead of counting id, you can use Sum(Month(date)=1) like in kander's answer). Of course you can combine both queries into this:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan_Tot,
  Count(case when month(`date`)=1 then `Attribute` end) As Jan_Attr,
  Count(case when month(`date`)=2 then id end) As Feb_Tot,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb_Attr,
  Count(case when month(`date`)=3 then id end) As Mar_Tot,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar_Attr,
  ...
FROM your_table
GROUP BY Year(`date`)

Mysql cumulative sum and group by month year

Try this :

select date_format(s1.date,'%b-%Y') as month,
       (select sum(s2.value) 
        from saving s2 
        where s2.date <= last_day(s1.date)) as total
from saving s1
group by month
order by s1.date
limit 50;

You can see more information on this question :

Create a Cumulative Sum Column in MySQL

isn't in GROUP BY with DAY, MONTH, YEAR

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

GROUP BY month on DATETIME field

Could you try this?

select count(*), DATE_FORMAT(timestamp, "%Y-%m-01")
from title
group by DATE_FORMAT(timestamp, "%Y-%m-01")

Please, note that MONTH() can't differentiate '2013-01-01' and '2014-01-01' as follows.

mysql> SELECT MONTH('2013-01-01'), MONTH('2014-01-01');
+---------------------+---------------------+
| MONTH('2013-01-01') | MONTH('2014-01-01') |
+---------------------+---------------------+
|                   1 |                   1 |
+---------------------+---------------------+

MySQL GROUP BY YEAR(), MONTH() returns dates sometimes at start of month, sometimes at end

When you select a column while grouping, it gets that column from a random row in the group. If you want a specific value, you have to say so with an aggregation function:

SELECT MIN(datefield) first_day_of_month
FROM Calendar
GROUP BY YEAR(datefield), MONTH(datefield)

How to fix group by and order by year and months in sql?

You can order by the minimum date in each range:

SELECT DATE_FORMAT(created_at, '%Y %b') ym 
FROM `book_summaries` 
GROUP BY DATE_FORMAT(created_at, '%Y %b') 
ORDER BY MIN(created_at);

Note that I also replaced the double quotes with single quotes. Single quotes are the SQL standard for strings (although some database do support double quotes as a string delimiter).

---

Related Topics