create table tt( col1 varchar2(10), col2 number ) insert into tt values('a',5); insert into tt values('a',4); insert into tt values('b',3); insert into tt values('a',7); insert into tt values('b',6); SQL> select col1,sum(col2) from tt 2 group by col1 3 /COL1 SUM(COL2) ---------- ---------- a 16 b 9
哈,不好意思,少写了个group by
select col1 as col1_type, sum(col2) as sum_col2 from t1 group by col1 应该是最优的了。
col1 varchar2(10),
col2 number
)
insert into tt values('a',5);
insert into tt values('a',4);
insert into tt values('b',3);
insert into tt values('a',7);
insert into tt values('b',6);
SQL> select col1,sum(col2) from tt
2 group by col1
3 /COL1 SUM(COL2)
---------- ----------
a 16
b 9
sum(col2) as sum_col2
from t1
group by col1
应该是最优的了。
col_a col_b
16 9另外还有一个问题,如果想输出如下结果(也就是分类count),应该怎么写?
t3:
col_a col_b
3 2
from (select decode(col1,'a',col2,0) col_a,decode(col1,'b',col2,0) col_b from t1)
select sum(decode(col_a,'a',1,0)) col_a,
sum(decode(col_b,'b',1,0)) col_b
from t1