0 votes
1 view
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:

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!

1 Answer

0 votes
by (40.3k points)

Try this code:

GROUP BY YEAR(Record_date), MONTH(Record_date)

OR

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.

Example: 

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
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...