开发者

Counting multiple data query for a single column

I have got a column name state(int) in which only 0 and 1 are inserted depending on the logic, I want 开发者_StackOverflow社区a count query which can count how many 0 and how many 1 are there in the table in a single query like:

Select Count(state) From productDetail......


select
    sum(state) countOfOnes,
    sum(decode(state, 0, 1, 0)) countOfZeros 
from
    productDetail
;

or

select
    sum(state) countOfOnes,
    count(*) - sum(state) countOfZeros 
from
    productDetail
;

or

select
    state, 
    count(*) over (partition by state order by state) countOfState
from
    productDetail
;

The first two examples will return one row with two columns:

countOfOnes  countOfZeros
=========================
154          21  

The third example will return two rows with two columns, one row per state.

state        countOfState
=========================
0            21
1            154  


Another variant:

select 
  count(*) countOverall,
  sum(state) countOfOnes,
  count(*) - sum(state) countOfZeroes
from
  productDetail;


select count(state) from productDetail Where state = 0
select count(state) from productDetail Where state = 1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜