CODEWITHSUNDEEP
sqlpostgresql
Jon Smark
Jon Smark

Reputation: 2608

Obtaining counts for two different conditions

Suppose I have a table states as follows:

create table states (id int4 unique not null, state int4 not null);

I want to get the number of rows in the table that whose state is 1, and the number of rows in the table whose state is 2. Doing this in two separate queries is straightforward:

select count(*) from states where state = 1;
select count(*) from states where state = 2;

But it seems silly to go through the whole table twice. Can someone think of some clever trick that would allow me to get the same information in a single statement and going through the table only once?

Upvotes: 3

Views: 110

Answers (4)

Tamoor
Tamoor

Reputation: 11

SELECT [state], COUNT(*) TotalRows 
FROM [Expense].[dbo].[state]
GROUP BY [state]

Assume you are using SQL server

Upvotes: 1

mewosic
mewosic

Reputation: 485

select count(*) from states group by state having state = 1 or state=2;

Use SQL group by

Upvotes: 1

Taryn
Taryn

Reputation: 247650

You can use a CASE with an aggregate function:

select
  sum(case when state=1 then 1 else 0 end) State1,
  sum(case when state=2 then 1 else 0 end) State2
from states

Upvotes: 5

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select
    count((state = 1)::integer or null) state_1,
    count((state = 2)::integer or null) state_2
from states

or

select
    sum((state = 1)::integer) state_1,
    sum((state = 2)::integer) state_2
from states

Upvotes: 5

Related Questions