MySQL statistical function records

Summary by year, statistics:

Select sum (mymoney) as totalmoney, count (*) as sheets from mytable group by date_format (col, '% y ');

Monthly summary, statistics:

Select sum (mymoney) as totalmoney, count (*) as sheets from mytable group by date_format (col, '% Y-% m ');

Quarterly summary and statistics:

Select sum (mymoney) as totalmoney, count (*) as sheets from mytable group by concat (date_format (col, '% y'), FLOOR (date_format (col, '% m') + 2)/3 ));

Select sum (mymoney) as totalmoney, count (*) as sheets from mytable group by concat (date_format (col, '% y'), FLOOR (date_format (col, '% m') + 2)/3 ));

By hour:

Select sum (mymoney) as totalmoney, count (*) as sheets from mytable group by date_format (col, '% Y-% m-% d % H ');

Query data for the current year:

SELECT * FROM mytable WHERE year (FROM_UNIXTIME (my_time) = year (curdate ())

Number of accompanying quarters of query data:

SELECT id, quarter (FROM_UNIXTIME (my_time) FROM mytable;

Query data for the current quarter:

SELECT * FROM mytable WHERE quarter (FROM_UNIXTIME (my_time) = quarter (curdate ());

Statistics for this month:

Select * from mytable where month (my_time1) = month (curdate () and year (my_time2) = year (curdate ())

Weekly Statistics:

Select * from mytable where month (my_time1) = month (curdate () and week (my_time2) = week (curdate ())

Records within N days:

WHERE TO_DAYS (NOW ()-TO_DAYS (time field) <= N

1 2 3 4 5 Next Page

[Content navigation]
Page 1: Time period Statistics	Page 1: GROUP_CONCAT () function
Page 1: count by condition	Page 4th: Join details
Page 1: Numeric Functions