sql语句分别按日,按周,按月,按季统计金额
如:
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
-- 按日
select sum (consume), day ( [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by day ( [ date ] )
-- 按周quarter
select sum (consume), datename (week, [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by datename (week, [ date ] )
-- 按月
select sum (consume), month ( [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by month ( [ date ] )
-- 按季
select sum (consume), datename (quarter, [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by datename (quarter, [ date ] )
-- 指定日期你就看上面的例子变通下呀,无非就是一个聚合函数和Group by
select [ date ] , sum (consume) from consume_record where [ date ] between ' 2006-06-01 ' and ' 2006-07-10 ' group by [ date ]
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
-- 按日
select sum (consume), day ( [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by day ( [ date ] )
-- 按周quarter
select sum (consume), datename (week, [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by datename (week, [ date ] )
-- 按月
select sum (consume), month ( [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by month ( [ date ] )
-- 按季
select sum (consume), datename (quarter, [ date ] ) from consume_record where year ( [ date ] ) = ' 2006 ' group by datename (quarter, [ date ] )
-- 指定日期你就看上面的例子变通下呀,无非就是一个聚合函数和Group by
select [ date ] , sum (consume) from consume_record where [ date ] between ' 2006-06-01 ' and ' 2006-07-10 ' group by [ date ]