这种统计用语句怎么写？
id carid count date 
1 200710 50 2007-10-21
2 200710A 30 2007-10-21
3 200711 80 2007-10-21
4 200712 40 2007-10-21
5 200712A 20 2007-10-21

。。。。。。。。。。。。。。。。。。more

其中id 是自动的，carid带A的包含在不带A的数里，carid 只有这××××××和××××××A这两种形式

如何能统计出

carid countA count  
200710 30 50
200711 0 80
200712 20 40
。。。。。。。。。。。。。more
不知道大家看明白没有？

------解决方案--------------------
create table table1(id int,carid varchar(7),[count] int,[date] datetime);
go
insert into table1
select 1,'200710 ',50,'2007-10-21'
union select 2,'200710A',30,'2007-10-21'
union select 3,'200711 ',80,'2007-10-21'
union select 4,'200712 ',40,'2007-10-21'
union select 5,'200712A',20,'2007-10-21'
go
select * from table1
select left(carid,6) as carid,sum(case when carid like '%A' then count else 0 end) as countA,sum(case when carid like '%A' then 0 else count end) as count from table1 group by left(carid,6)
go
drop table table1



(5 行受影响)
id carid count date
----------- ------- ----------- -----------------------
1 200710 50 2007-10-21 00:00:00.000
2 200710A 30 2007-10-21 00:00:00.000
3 200711 80 2007-10-21 00:00:00.000
4 200712 40 2007-10-21 00:00:00.000
5 200712A 20 2007-10-21 00:00:00.000

(5 行受影响)

carid countA count
------- ----------- -----------
200710 30 50
200711 0 80
200712 20 40

(3 行受影响)


------解决方案--------------------

SQL code

create table tb(id int, carid varchar(10),[count] int, date datetime)
insert into tb values(1,    '200710' ,   50,     '2007-10-21') 
insert into tb values(2,    '200710A',   30,     '2007-10-21') 
insert into tb values(3,    '200711' ,   80,     '2007-10-21') 
insert into tb values(4,    '200712' ,   40,     '2007-10-21') 
insert into tb values(5,    '200712A',   20,     '2007-10-21') 
go

select t1.carid , isnull(t2.countA,0) countA,t1.[count] from
(select left(carid,6) carid, sum([count]) [count] from tb group by left(carid,6)) t1 
left join
(select carid , sum([count]) countA from tb where len(carid) = 7 group by carid) t2
on t1.carid = left(t2.carid,6)

drop table tb

/*
carid      countA      count       
---------- ----------- ----------- 
200710     30          80
200711     0           80
200712     20          60

（所影响的行数为 3 行）
*/