SQL> select * from tbl;A B ---------------------------------------- --------------------------------------- a 1 a 2 b a SQL> SQL> select a, sum(case when (b='' or b is null) then 0 else 1 end) total from tbl 2 group by a;A TOTAL ---------------------------------------- ---------- a 2 b 0
分组字段是不是还有其他表储存啊? 只是这一个表的话出不来的,因为我不知道还有'name3' 假设编码表为TABLEA select A,(SELECT count(B)FROM TABLE WHERE B=0 AND A=TABLEA.A) AS 计数 from TABLEA
select A, sum(nvl(decode(B,'0',1),0)) as CountB from table group by A order by A hah ,用这个
正确如下:select b.tran_etc1, b.numTotal as collectivity, nvl(a.success,0) as success, (b.numTotal - nvl(a.success,0)) as FAILURE from (select tran_etc1, nvl(count(tran_etc1),0) as success from em_log_200808 where tran_rslt = 0 group by tran_etc1) a,
(select tran_etc1, count(tran_etc1) as numTotal from em_log_200808 group by tran_etc1) b where b.tran_etc1 = a.tran_etc1(+)
直接:
SELECT A, COUNT(B) AS NUM0 FROM TABLE1 WHERE B = 0 GROUP BY A;
就可以的.
from table
where B = 0
group by 分组字段
不行的..
那样的话..就象我上面写的一样..
Name3计数=0的话..他就不会显示出来的..
你可以试下..
二楼三楼也不行.
SQL> select * from tbl;A B
---------------------------------------- ---------------------------------------
a 1
a 2
b
a SQL>
SQL> select a, sum(case when (b='' or b is null) then 0 else 1 end) total from tbl
2 group by a;A TOTAL
---------------------------------------- ----------
a 2
b 0
只是这一个表的话出不来的,因为我不知道还有'name3'
假设编码表为TABLEA
select A,(SELECT count(B)FROM TABLE WHERE B=0 AND A=TABLEA.A) AS 计数
from TABLEA
from table
group by A
order by A
hah ,用这个
b.numTotal as collectivity,
nvl(a.success,0) as success,
(b.numTotal - nvl(a.success,0)) as FAILURE
from (select tran_etc1,
nvl(count(tran_etc1),0) as success
from em_log_200808
where tran_rslt = 0
group by tran_etc1) a,
(select tran_etc1, count(tran_etc1) as numTotal
from em_log_200808
group by tran_etc1) b
where b.tran_etc1 = a.tran_etc1(+)
decode(B,'0',1) 表示当B列的值为“0”时,做 1 计算,
nvl( decode(B,'0',1),0)表示当B列的值不为零的时候,做 0 计算