Mysql查询统计按年月日（年、季、月、周、日）

Mysql查询统计：按年、季、月、周、日

按年统计交易金额。

SELECT
	sum(goods_fee),
	YEAR (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)
FROM
	goods_order
WHERE
	pay_status = 1
GROUP BY
	YEAR (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)

按季统计交易金额。

SELECT
	sum(goods_fee),
	QUARTER (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)
FROM
	goods_order
WHERE
	pay_status = 1
AND FROM_UNIXTIME(create_time, '%Y') = '2018'
GROUP BY
	QUARTER (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)

按月统计交易金额。

SELECT
	sum(goods_fee),
	MONTH (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)
FROM
	goods_order
WHERE
	pay_status = 1
AND FROM_UNIXTIME(create_time, '%Y') = '2018'
GROUP BY
	MONTH (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)

按周统计交易金额。

SELECT
	sum(goods_fee),
	WEEK (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)
FROM
	goods_order
WHERE
	pay_status = 1
AND FROM_UNIXTIME(create_time, '%Y-%m') = '2019-03'
GROUP BY
	WEEK (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)

按日统计交易金额。

SELECT
	sum(goods_fee),
	DAY (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)
FROM
	goods_order
WHERE
	pay_status = 1
AND FROM_UNIXTIME(create_time, '%Y-%m') = '2019-02'
GROUP BY
	DAY (
		FROM_UNIXTIME(create_time, '%Y-%m-%d')
	)