Question

SQL字段累加求和的问题

现有表table
结构如下:
====================
id(int) string1(varchar) string2(int)
1 X 1
2 X 0
3 X 1
4 Y 0
5 Y 1
6 Y 1
我想将String1字段数据相同在临时字段中生成出现的次数,再将String2字段的数字累加求和,用SQL语句,或者存储过程,或者ASP页面程序都可以.麻烦大家了.

Answer 1

不用那么麻烦的，一个select查询就可以了。不用子查询的

select string1,count(string1) as 次数,sum(string2) as 累计 from table1 group by string1

Answer 2

String2 累加是要的相同string1的和还是总和,如果是相同string1的话
select count(string1),sum(string2) from table group by string1

如果要总和,可以另写一条
select count(string1) from table
group by string1
union all
select sum(string2) from table

最后1条是总数

Answer 3

SELECT ID,STRING1,STRING2 from table UNION
select ID=0,string1=string1+'小计',countresult=count(1),sum1=sum(string2) from talbe group by string1 union
select ID=0,string1='总计',countresult=count(1),sum1=sum(string2) from table order by string1,id
结果为包括了小计和总计,和明细,并按顺序排列

Answer 4

select temptable.string1, temptable.次数 , sum(b.string2)
from
(select String1,count(0) as 次数 from table group by String1) as temptable , table b
where temptable .String1 = b.String1
group by temptable.string1, temptable.次数

Answer 5

jyh_jack

的答案正解！