CODEWITHSUNDEEP
sqlcountgroup-by
Firefox
Firefox

Reputation: 951

Retrieving two COUNTs using a single SQL statement

Assume Table1 has c1 column for which allowed values are either 1 or 0.

How to retrieve number of 0s, 1s with a single query? Is it possible?

sorry, STUPID way of questioning.

I wanted the answer in a single row, not single column.

Upvotes: 2

Views: 135

Answers (6)

jack.mike.info
jack.mike.info

Reputation: 128

select uid from emp

UID 0 0 0 1 1 1 1 0 0

select distinct

(select count(uid) from emp  where uid = 0) zeros_total,

(select count(uid) from emp where uid = 1) ones_total

from 
emp

O/P **zeros_total 5 ones_total 4**

Upvotes: 0

Alex K.
Alex K.

Reputation: 175806

Alternate way;

select
   sum(c1) as ones, 
   count(*) - sum(c1) as zeros
from
   Table1

Upvotes: 2

Cees Alberts
Cees Alberts

Reputation: 21

Depends on the DBMS you're using. In oracle the following is possible:

select
 (select count(status) from table1 where status = 0) as status_0,
 (select count(status) from table1 where status = 1) as status_1
from dual

Upvotes: 1

gbn
gbn

Reputation: 432271

This is a single table/index read. Subqueries will most likely have two such reads

SELECT
   COUNT(CASE status WHEN 1 THEN 1 ELSE NULL END) AS Ones,
   COUNT(CASE status WHEN 0 THEN 1 ELSE NULL END) AS Zeros
FROM
   MyTable

..and it's portable too

Upvotes: 3

Rich Parker
Rich Parker

Reputation: 172

Already been answered, but there's always (nearly) more than one way to do it. Union of two queries? 

select count(*) from t1 where value='0' 
union
select count(*) from t1 where value='1'

Upvotes: 0

Bjoern
Bjoern

Reputation: 16304

If I understand this correctly, just GROUP BY that column.

SELECT c1, count(*)
FROM Table1
GROUP BY c1;

Upvotes: 2

Related Questions