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
精彩评论