有一表,数据如下:
ID Date Count
1 2008-08-01 11
1 2008-08-02 11
1 2008-08-03 22
1 2009-08-01 11
1 2009-08-02 22
1 2009-08-03 22
1 2009-09-01 11
1 2009-09-02 22
1 2009-09-03 33我想统计出:
ID Date Count
1 2008-08 44
1 2009-08 55
1 2009-09 66
sql 语句应该怎么写,请高手指点~
ID Date Count
1 2008-08-01 11
1 2008-08-02 11
1 2008-08-03 22
1 2009-08-01 11
1 2009-08-02 22
1 2009-08-03 22
1 2009-09-01 11
1 2009-09-02 22
1 2009-09-03 33我想统计出:
ID Date Count
1 2008-08 44
1 2009-08 55
1 2009-09 66
sql 语句应该怎么写,请高手指点~
from tb
group by ID,Date
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-02 14:07:06
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([ID] int,[Date] Datetime,[Count] int)
Insert [tb]
Select 1,'2008-08-01',11 union all
Select 1,'2008-08-02',11 union all
Select 1,'2008-08-03',22 union all
Select 1,'2009-08-01',11 union all
Select 1,'2009-08-02',22 union all
Select 1,'2009-08-03',22 union all
Select 1,'2009-09-01',11 union all
Select 1,'2009-09-02',22 union all
Select 1,'2009-09-03',33
Go
--Select * from [tb]-->SQL查询如下:
select ID,convert(char(7),Date,23) Date,sum([Count]) as [Count]
from tb
group by ID,convert(char(7),Date,23)
/*
ID Date Count
1 2008-08 44
1 2009-08 55
1 2009-09 66
*/
select ID,CONVERT(VARCHAR(7),Date,120),SUM(Count) as [Count]
from tb
group by ID,CONVERT(VARCHAR(7),Date,120)
ID,
convert(char(7),Date,120) as Date,
sum([Count]) as [Count]
from
tb
group by
ID,convert(char(7),Date,23)
ID,
convert(char(7),Date,120) as Date,
sum([Count]) as [Count]
from
tb
group by
ID,convert(char(7),Date,120)
If not object_id('tb') is null
Drop table [tb]
GoCreate table tb(
ID int ,dt smalldatetime,m int
)
Go
Insert into tb
select 1,'2008-08-02',11 union all
select 1,'2008-08-03',22 union all
select 1,'2009-08-01',11 union all
select 1,'2009-08-02',22 union all
select 1,'2009-08-03',22 union all
select 1,'2009-09-01',11 union all
select 1,'2009-09-02',22 union all
select 1,'2009-09-03',33
--Start
select
ID,
convert(varchar(7),dt,120) as dt,
sum(m) as mCount
from
tb
group by
convert(varchar(7),dt,120),ID