select year(tran_date), month(tran_date), avg(tran_amount) from (select tran_date, sum(tran_amount) as tran_amount from TableA group by tran_date) s group by year(tran_date), month(tran_date)Note: 1.子查询是避免你的交易表同一天里面有多条记录; 2.请注意AVG参数排除空值,如果一个月里面其中有几天没有记录的话就不会参与平均计算了
from (select tran_date, sum(tran_amount) as tran_amount from TableA group by tran_date) s
group by year(tran_date), month(tran_date)Note:
1.子查询是避免你的交易表同一天里面有多条记录;
2.请注意AVG参数排除空值,如果一个月里面其中有几天没有记录的话就不会参与平均计算了