MySQL Query GROUP BY day month year

Question

Is it possible to make a simple query to count how many records I have in a determined period of time like a year  month  or day  having a TIMESTAMP field  like    SELECT COUNT id  FROM stats WHERE record date YEAR   2009 GROUP BY record date YEAR   Or even   SELECT COUNT id  FROM stats GROUP BY record date YEAR  record date MONTH   To have a monthly statistic    Thanks

User · Answer

If you want to group by date in MySQL then use the code below:

 SELECT COUNT(id)
 FROM stats
 GROUP BY DAYOFMONTH(record_date)

Hope this saves some time for the ones who are going to find this thread.

User · Answer

Here s one more approach   This uses  MySQL s LAST DAY   function  1  to map each timestamp to its month  It also is capable of filtering by year with an efficient range-scan if there s an index on record date    SELECT LAST DAY record date  month ending  COUNT    record count     FROM stats    WHERE record date  gt    2000-01-01       AND record date  lt    2000-01-01    INTERVAL 1 YEAR    GROUP BY LAST DAY record date    If you want your results by day  use DATE record date  instead  If you want your results by calendar quarter  use YEAR record date   QUARTER record date   Here s a writeup  https   www plumislandmedia net mysql sql-reporting-time-intervals   1   https   dev mysql com doc refman 8 0 en date-and-time-functions html function last-day

User · Answer

If you want to group by date in MySQL then use the code below:

 SELECT COUNT(id)
 FROM stats
 GROUP BY DAYOFMONTH(record_date)

Hope this saves some time for the ones who are going to find this thread.

User · Answer

try this one  SELECT COUNT id  FROM stats GROUP BY EXTRACT YEAR MONTH FROM record date    EXTRACT unit FROM date  function is better as less grouping is used and the function return a number value   Comparison condition when grouping will be faster than DATE FORMAT function  which return a string value   Try using function field that return non-string value for SQL comparison condition  WHERE  HAVING  ORDER BY  GROUP BY

User · Answer

try this one  SELECT COUNT id  FROM stats GROUP BY EXTRACT YEAR MONTH FROM record date    EXTRACT unit FROM date  function is better as less grouping is used and the function return a number value   Comparison condition when grouping will be faster than DATE FORMAT function  which return a string value   Try using function field that return non-string value for SQL comparison condition  WHERE  HAVING  ORDER BY  GROUP BY

User · Answer

Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active   SELECT COUNT    FROM stats -- GROUP BY YEAR record date   MONTH record date   DAYOFMONTH record date  GROUP BY DATE FORMAT record date    Y- m- d

User · Answer

group by to char date   YYYY   --  1989       group by to char date  MM       -- 05       group by to char date  DD     --- 23       group by to char date  MON    --- MAY       group by to char date  YY     --- 89

User · Answer

GROUP BY DATE FORMAT record date    Y m      Note  primarily  to potential downvoters   Presently  this may not be as efficient as other suggestions  Still  I leave it as an alternative  and a one  too  that can serve in seeing how faster other solutions are   For you can t really tell fast from slow until you see the difference   Also  as time goes on  changes could be made to MySQL s engine with regard to optimisation so as to make this solution  at some  perhaps  not so distant  point in future  to become quite comparable in efficiency with most others

User · Answer

GROUP BY YEAR record date   MONTH record date    Check out the date and time functions in MySQL

User · Answer

GROUP BY YEAR record date   MONTH record date    Check out the date and time functions in MySQL

User · Answer

I prefer to optimize the one year group selection like so   SELECT COUNT      FROM stats  WHERE record date  gt    year     AND record date  lt    year   INTERVAL 1 YEAR    This way you can just bind the year in once  e g   2009   with a named parameter and don t need to worry about adding  -01-01  or passing in  2010  separately   Also  as presumably we are just counting rows and id is never NULL  I prefer COUNT    to COUNT id

User · Answer

The following query worked for me in Oracle Database 12c Release 12 1 0 1 0  SELECT COUNT    FROM stats GROUP BY  extract MONTH FROM TIMESTAMP   extract MONTH FROM TIMESTAMP   extract YEAR  FROM TIMESTAMP

User · Answer

I wanted to get similar data per day  after experimenting a bit  this is the fastest I could find for my scenario SELECT COUNT id  FROM stats GROUP BY record date DIV 1000000   If you want to have it per month  add additional zeroes  00  I would not recommend this since it is not clear why or how this works  it might also break in different versions  But in our case this took less then half the time compared to some other more clearer queries that I tested

User · Answer

If your search is over several years  and you still want to group monthly  I suggest   version  1   SELECT SQL NO CACHE YEAR record date   MONTH record date   COUNT    FROM stats GROUP BY DATE FORMAT record date    Y m     version  2  more efficient    SELECT SQL NO CACHE YEAR record date   MONTH record date   COUNT    FROM stats GROUP BY YEAR record date  100   MONTH record date    I compared these versions on a big table with 1 357 918 rows  innodb   and the 2nd version appears to have better results   version1  average of 10 executes    1 404 seconds  version2  average of 10 executes    0 780 seconds    SQL NO CACHE key added to prevent MySQL from CACHING to queries

User · Answer

GROUP BY DATE FORMAT record date    Y m      Note  primarily  to potential downvoters   Presently  this may not be as efficient as other suggestions  Still  I leave it as an alternative  and a one  too  that can serve in seeing how faster other solutions are   For you can t really tell fast from slow until you see the difference   Also  as time goes on  changes could be made to MySQL s engine with regard to optimisation so as to make this solution  at some  perhaps  not so distant  point in future  to become quite comparable in efficiency with most others

User · Answer

Here s one more approach   This uses  MySQL s LAST DAY   function  1  to map each timestamp to its month  It also is capable of filtering by year with an efficient range-scan if there s an index on record date    SELECT LAST DAY record date  month ending  COUNT    record count     FROM stats    WHERE record date  gt    2000-01-01       AND record date  lt    2000-01-01    INTERVAL 1 YEAR    GROUP BY LAST DAY record date    If you want your results by day  use DATE record date  instead  If you want your results by calendar quarter  use YEAR record date   QUARTER record date   Here s a writeup  https   www plumislandmedia net mysql sql-reporting-time-intervals   1   https   dev mysql com doc refman 8 0 en date-and-time-functions html function last-day

User · Answer

If you want to get a monthly statistics with row counts per month of each year ordered by latest month  then try this   SELECT count id         YEAR record date         MONTH record date   FROM  table   GROUP BY YEAR record date           MONTH record date   ORDER BY YEAR record date  DESC          MONTH record date  DESC

User · Answer

If your search is over several years  and you still want to group monthly  I suggest   version  1   SELECT SQL NO CACHE YEAR record date   MONTH record date   COUNT    FROM stats GROUP BY DATE FORMAT record date    Y m     version  2  more efficient    SELECT SQL NO CACHE YEAR record date   MONTH record date   COUNT    FROM stats GROUP BY YEAR record date  100   MONTH record date    I compared these versions on a big table with 1 357 918 rows  innodb   and the 2nd version appears to have better results   version1  average of 10 executes    1 404 seconds  version2  average of 10 executes    0 780 seconds    SQL NO CACHE key added to prevent MySQL from CACHING to queries

User · Answer

Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active   SELECT COUNT    FROM stats -- GROUP BY YEAR record date   MONTH record date   DAYOFMONTH record date  GROUP BY DATE FORMAT record date    Y- m- d

User · Answer

You can do this simply Mysql DATE FORMAT   function in GROUP BY  You may want to add an extra column for added clarity in some cases such as where records span several years then same month occurs in different years Here so many option you can customize this  Please read this befor starting  Hope it should be very helpful for you  Here is sample query for your understanding  SELECT     COUNT id       DATE FORMAT record date    Y- m- d   AS DAY      DATE FORMAT record date    Y- m   AS MONTH      DATE FORMAT record date    Y   AS YEAR  FROM     stats WHERE     YEAR   2009 GROUP BY     DATE FORMAT record date    Y- m- d

User · Answer

GROUP BY YEAR record date   MONTH record date    Check out the date and time functions in MySQL

User · Answer

If you want to filter records for a particular year  e g  2000  then optimize the WHERE clause like this   SELECT MONTH date column   COUNT    FROM date table WHERE date column  gt    2000-01-01  AND date column  lt   2001-01-01  GROUP BY MONTH date column  -- average 0 016 sec    Instead of   WHERE YEAR date column    2000 -- average 0 132 sec    The results were generated against a table containing 300k rows and index on date column   As for the GROUP BY clause  I tested the three variants against the above mentioned table  here are the results   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY YEAR date column   MONTH date column  -- codelogic -- average 0 250 sec   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY DATE FORMAT date column    Y m   -- Andriy M -- average 0 468 sec   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY EXTRACT YEAR MONTH FROM date column  -- fu-chi -- average 0 203 sec    The last one is the winner

User · Answer

I prefer to optimize the one year group selection like so   SELECT COUNT      FROM stats  WHERE record date  gt    year     AND record date  lt    year   INTERVAL 1 YEAR    This way you can just bind the year in once  e g   2009   with a named parameter and don t need to worry about adding  -01-01  or passing in  2010  separately   Also  as presumably we are just counting rows and id is never NULL  I prefer COUNT    to COUNT id

User · Answer

You can do this simply Mysql DATE FORMAT   function in GROUP BY  You may want to add an extra column for added clarity in some cases such as where records span several years then same month occurs in different years Here so many option you can customize this  Please read this befor starting  Hope it should be very helpful for you  Here is sample query for your understanding  SELECT     COUNT id       DATE FORMAT record date    Y- m- d   AS DAY      DATE FORMAT record date    Y- m   AS MONTH      DATE FORMAT record date    Y   AS YEAR  FROM     stats WHERE     YEAR   2009 GROUP BY     DATE FORMAT record date    Y- m- d

User · Answer

I wanted to get similar data per day  after experimenting a bit  this is the fastest I could find for my scenario SELECT COUNT id  FROM stats GROUP BY record date DIV 1000000   If you want to have it per month  add additional zeroes  00  I would not recommend this since it is not clear why or how this works  it might also break in different versions  But in our case this took less then half the time compared to some other more clearer queries that I tested

User · Answer

I tried using the  WHERE  statement above  I thought its correct since nobody corrected it but I was wrong  after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this   SELECT COUNT id    FROM stats   WHERE YEAR record date    2009   GROUP BY MONTH record date

User · Answer

GROUP BY YEAR record date   MONTH record date    Check out the date and time functions in MySQL

User · Answer

group by to char date   YYYY   --  1989       group by to char date  MM       -- 05       group by to char date  DD     --- 23       group by to char date  MON    --- MAY       group by to char date  YY     --- 89

User · Answer

If you want to get a monthly statistics with row counts per month of each year ordered by latest month  then try this   SELECT count id         YEAR record date         MONTH record date   FROM  table   GROUP BY YEAR record date           MONTH record date   ORDER BY YEAR record date  DESC          MONTH record date  DESC

User · Answer

The following query worked for me in Oracle Database 12c Release 12 1 0 1 0  SELECT COUNT    FROM stats GROUP BY  extract MONTH FROM TIMESTAMP   extract MONTH FROM TIMESTAMP   extract YEAR  FROM TIMESTAMP

User · Answer

If you want to filter records for a particular year  e g  2000  then optimize the WHERE clause like this   SELECT MONTH date column   COUNT    FROM date table WHERE date column  gt    2000-01-01  AND date column  lt   2001-01-01  GROUP BY MONTH date column  -- average 0 016 sec    Instead of   WHERE YEAR date column    2000 -- average 0 132 sec    The results were generated against a table containing 300k rows and index on date column   As for the GROUP BY clause  I tested the three variants against the above mentioned table  here are the results   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY YEAR date column   MONTH date column  -- codelogic -- average 0 250 sec   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY DATE FORMAT date column    Y m   -- Andriy M -- average 0 468 sec   SELECT YEAR date column   MONTH date column   COUNT    FROM date table GROUP BY EXTRACT YEAR MONTH FROM date column  -- fu-chi -- average 0 203 sec    The last one is the winner

User · Answer

I tried using the  WHERE  statement above  I thought its correct since nobody corrected it but I was wrong  after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this   SELECT COUNT id    FROM stats   WHERE YEAR record date    2009   GROUP BY MONTH record date

[mysql] MySQL Query GROUP BY day / month / year

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to datetime

Examples related to group-by