表中有以下记录:number date
8 2007-01-01
4 2007-01-10
6 2007-01-106 2007-02-01
8 2007-02-02
4 2007-02-02
6 2007-02-037 2007-03-01
我想按月统计number的和跟当月的number的日均值,我想要的结果如下:和 日均值 month
18 9 2007-01
24 8 2007-02
7 7 2007-03
注意:表中date是不唯一的,即有可能一天有多条记录
8 2007-01-01
4 2007-01-10
6 2007-01-106 2007-02-01
8 2007-02-02
4 2007-02-02
6 2007-02-037 2007-03-01
我想按月统计number的和跟当月的number的日均值,我想要的结果如下:和 日均值 month
18 9 2007-01
24 8 2007-02
7 7 2007-03
注意:表中date是不唯一的,即有可能一天有多条记录
SUM(number) As 和,
AVG(number) As 日均值,
Convert(Varchar(7), [date], 120) As [month]
From
表
Group By
Convert(Varchar(7), [date], 120)
Order By
[month]
insert @t
select 8, '2007-01-01' union all
select 4, '2007-01-10' union all
select 6, '2007-01-10' union all
select 6, '2007-02-01' union all
select 8, '2007-02-02' union all
select 4, '2007-02-02' union all
select 6, '2007-02-03' union all
select 7, '2007-03-01'select sum(number) as 和,
sum(number)/count(distinct [date]) as 日均值,
convert(varchar(7),[date],120) as [month]
from @t as a group by convert(varchar(7),[date],120)/*结果
和 日均值 month
-------------------------------
18 9 2007-01
24 8 2007-02
7 7 2007-03
*/
Select
SUM(number) As 和,
AVG(number) As 日均值,
Convert(Varchar(7), [date], 120) As [month]
From
表
Group By
Convert(Varchar(7), [date], 120)
Order By
[month]--如果date是字符類型
Select
SUM(number) As 和,
AVG(number) As 日均值,
Left([date], 7) As [month]
From
表
Group By
Left([date], 7)
Order By
[month]
--如果date是datetime類型
Select
SUM(number) As 和,
SUM(number) / Count(Distinct [date]) As 日均值,
Convert(Varchar(7), [date], 120) As [month]
From
表
Group By
Convert(Varchar(7), [date], 120)
Order By
[month]--如果date是字符類型
Select
SUM(number) As 和,
SUM(number) / Count(Distinct [date]) As 日均值,
Left([date], 7) As [month]
From
表
Group By
Left([date], 7)
Order By
[month]
不能直接使用AVG(),楼主是要求消除重复日期的.
-----------
貼上去之後就看到了這個問題。 :)
insert into t_sum(number,date) select
8, '2007-01-01' union all select
4, '2007-01-10' union all select
6 , '2007-01-10' union all select6, '2007-02-01' union all select
8, '2007-02-02' union all select
4, '2007-02-02' union all select
6 , '2007-02-03' union all select7 , '2007-03-01'select a.sum,a.sum/b.c,a.c_date from
(select sum(t_a.number) as sum,t_a.c_date from (select *,substring(convert(varchar(20),date,120),1,7) as c_date from t_sum)as t_a group by t_a.c_date) as a join
(select t_b.c_date,count(date) as c from (select distinct date,substring(convert(varchar(20),date,120),1,7) as c_date from t_sum) as t_b group by t_b.c_date) as b
on a.c_date=b.c_datedrop table t_sum
select sum(number),sum(number)/count(distinct date),convert(varchar(7),date,120) from t_sum group by convert(varchar(7),date,120)
SUM(SUMnumber) As 和,
SUM(SUMnumber) / Count([date]) As 日均值,
Format(date, "yyyy-MM") As [month]
From
(Select
SUM(number) As SUMnumber,
date
From
表
Group By date) A
Group By
Format(date, "yyyy-MM")
Order By
Format(date, "yyyy-MM")