Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

Is it possible I 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:


FROM stats

WHERE record_date.YEAR = 2009

GROUP BY record_date.YEAR

Or even:


FROM stats

GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.


1 Answer

0 votes
by (40.7k points)

Try this code:

GROUP BY YEAR(Record_date), MONTH(Record_date)


You can use DATE_FORMAT operator. If you are using this you can easily group the date, timestamp or datetime column using whatever format you want.


If you want to group rows that were added on the same day. 

Use this query:

Select count(*), DATE_FORMAT(Created_At,"%Y-%m-%d") as Created_Day1 FROM Mydate1 GROUP BY Created_Day1

This query will give you the following result:

count(*) | Created_Day1

127 | 2013-05-13

169 | 2013-05-14

170 | 2013-05-15

190 | 2013-05-16

188 | 2013-05-17

132 | 2013-05-18

If you want to Group by month, then use this code:

Select count(*), DATE_FORMAT(Created_At,"%Y-%m") as Created_Month1 FROM Mydate1 GROUP BY Created_Month1

If you want to Group by year, then use this code:

select count(*), DATE_FORMAT(Created_At,"%Y") as Created_Year1 FROM Mydate1 GROUP BY Created_Year1

If you want to prepare for SQL developer jobs then go through this My SQL Interview Questions.

Related questions

0 votes
1 answer
asked Jul 15, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Browse Categories