CODEWITHSUNDEEP
oracle-databaset-sqlsql
Mehmet
Mehmet

Reputation: 2288

Complex SQL query

There is a customer table. I want to list active and inactive status in one query. How can I do this?

SELECT count(*) as ACTIVE, 
count(*) as INACTIVE 
FROM V_CUSTOMER 
WHERE STATUS='a' AND STATUS='i'

Upvotes: 2

Views: 961

Answers (4)

Pranay Rana
Pranay Rana

Reputation: 176956

We can use CASE statement to translate the two values of STATUS:

SELECT 
    sum(case when STATUS = 'a' then 1 else 0 end) as ACTIVE 
    , sum(case when STATUS = 'd' then 1 else 0 end) as DEACTIVE 
FROM V_CUSTOMER

There is no need for a WHERE clause unless there are a large number of records with other values for STATUS, in which case use OR instead of AND:

WHERE STATUS='a' OR STATUS='d'

Upvotes: 5

Ravi Kumar
Ravi Kumar

Reputation: 31

SELECT count(decode(status,'a',1)) as ACTIVE, 
count(decode(status,'d',1)) as DEACTIVE 
FROM V_CUSTOMER 
WHERE STATUS='a' or STATUS='d'

Upvotes: 3

Peter Eysermans
Peter Eysermans

Reputation: 489

I think you'll need something like this:

select Status, count(*) from V_Customer
where STATUS='a' or STATUS='d'
group by STATUS

This will give you the number of records per status.

Upvotes: 1

krock
krock

Reputation: 29629

Try using group by:

SELECT count(*), STATUS FROM V_CUSTOMER
Where STATUS='a' OR STATUS='d'
GROUP BY STATUS

Upvotes: 4

Related Questions