CODEWITHSUNDEEP
asp.netsqldatabasecount
Mr A
Mr A

Reputation: 6778

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 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......

Upvotes: 0

Views: 239

Answers (3)

Pankaj
Pankaj

Reputation: 10115

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

Upvotes: -1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174467

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

Upvotes: 3

Frank Schmitt
Frank Schmitt

Reputation: 30845

Another variant:

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

Upvotes: 1

Related Questions