MySQL Query GROUP BY day / month / year
GROUP BY YEAR(record_date), MONTH(record_date)
Check out the date and time functions 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 |
+---------------------+---------------------+
Group by month and year in MySQL
GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);
is what you want.
Group by day using the timestamp field
Use MySQL DATE()
function to extract the date from the timestamp:
SELECT name, count(*) FROM mytable GROUP BY DATE(mytable.modified);
The
DATE()
function extracts the date value from a date or datetime expression.
MySQL : Group by Datetime (Hour - Day - Month - Year)
Use DATE_FORMATE() in group By clause. Hope it should be solved your problem
SELECT
`client_id`,
`queue_id`,
`datetime`,
COUNT(`type`) AS nombre_appels_repondus
FROM
`appels`
WHERE
`type` = "non_repondu"
GROUP BY
`appels`.`client_id`,
`appels`.`queue_id`,
DATE_FORMAT(
`appels`.`datetime`,'%Y-%m-%d %H');
OR
If you want to add in your selected column list with day, month, year and hour then please try this one
SELECT
`client_id`,
`queue_id`,
`datetime`,
DATE_FORMAT(`appels`.`datetime`, '%Y-%m-%d') AS DAY,
DATE_FORMAT(`appels`.`datetime`, '%Y-%m') AS MONTH,
DATE_FORMAT(`appels`.`datetime`, '%Y') AS YEAR,
DATE_FORMAT(`appels`.`datetime`, '%H') AS HOUR,
COUNT(`type`) AS nombre_appels_repondus
FROM
`appels`
WHERE
`type` = "non_repondu"
GROUP BY
`appels`.`client_id`,
`appels`.`queue_id`,
DATE_FORMAT(
`appels`.`datetime`,
'%Y-%m-%d %H'
);
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`)
How to group by day/month/year on a subquery
Don't join the table again. Apply the grouping directly to the subquery:
SELECT YEAR(time), MONTHNAME(time), SUM(diff)
FROM (select EL.sensor_id,
EL.value_id,
EL.time,
EL.value,
if(@lastSN = EL.sensor_id, EL.value - @lastValue, 0.0) as diff,
@lastSN := EL.sensor_id,
@lastValue := EL.value
FROM data_test_debug EL, (SELECT @lastSN := 0,
@lastValue := 0) SQLVars
WHERE EL.value_id = "gas"
ORDER BY EL.sensor_id, EL.time
) AS t
GROUP BY YEAR(time), MONTH(time)
It can be greatly simplified by removing all those fields that you're not using, but you can do that after you get it working.
Related Topics
Activerecord Arel or Condition
Finding Duplicate Values in a SQL Table
How to Implement a Many-To-Many Relationship in Postgresql
How to Declare a Variable in MySQL
Optimal Way to Concatenate/Aggregate Strings
Auto Increment After Delete in MySQL
SQL Counting All Rows Instead of Counting Individual Rows
Ruby Gem MySQL2 Install Failing
SQL Split Values to Multiple Rows
Is There Any Rule of Thumb to Construct SQL Query from a Human-Readable Description
Which Is Faster/Best? Select * or Select Column1, Colum2, Column3, etc
How to Comma Delimit Multiple Rows into One Column
What Is the Major Difference Between Varchar2 and Char