Menu
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!
745
Group By Month Name In the above SQL query we use date_format(order_date, “%M”) to convert a date column into month name, and use sum column to add up the sales amounts.
1 Answer. 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.
We can group the resultset in SQL on multiple column values. When we define the grouping criteria on more than one column, all the records having the same value for the columns defined in the group by clause are collectively represented using a single record in the query output.
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
GROUP BY YEAR(record_date), MONTH(record_date) Check out the date and time functions in MySQL.
89
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
