complex sql query

Question

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

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

Answer 1

Try using group by:

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

Answer 2

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'         

Answer 3

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.

Answer 4

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