CODEWITHSUNDEEP
sqloracle-database
Tzereen
Tzereen

Reputation: 35

CASE WHEN with condition met and all results - SQL Oracle

I have a following table (clients):

client | status   | email | phone
---------------------------------
001    | active   | yes   | yes
002    | inactive | yes   | yes
003    | inactive | yes   | no
004    | deceased | no    | no
005    | active   | yes   | no
006    | deceased | no    | no
007    | active   | yes   | yes
008    | inactive | no    | yes
009    | active   | no    | no
010    | inactive | yes   | yes

I need results to show stats of inactive clients compared to all clients, ie.

status   | email | phone | total
--------------------------------
inactive | yes   | yes   | 2
inactive | yes   | no    | 1
inactive | no    | yes   | 1
all      | yes   | yes   | 4
all      | yes   | no    | 2
all      | no    | yes   | 1
all      | no    | no    | 3

Since CASE checks met conditions, I don't know how to get a fraction of results in first condition and then all results.

I used these two selects:

SELECT CASE WHEN STATUS = 'inactive' THEN STATUS 
            WHEN STATUS IS NOT NULL THEN 'all' END "STATUS",
       EMAIL, PHONE,
       COUNT(CLIENT) TOTAL
FROM CLIENTS
GROUP BY CASE WHEN STATUS = 'inactive' THEN STATUS 
              WHEN STATUS IS NOT NULL THEN 'all' END,
         EMAIL, PHONE
ORDER BY 1 DESC, 2 DESC, 3 DESC;
-------
SELECT 'all' AS STATUS, EMAIL, PHONE,
       COUNT(CLIENT) TOTAL
FROM CLIENTS
GROUP BY EMAIL, PHONE
ORDER BY 2 DESC, 3 DESC;

Obviously, first part is not working as intended, but I put it here to better showcase my question.

Is it possible to do it in one query?

Upvotes: 1

Views: 665

Answers (3)

Pelin
Pelin

Reputation: 966

You can use below query:

with stat ( status) as (
  select'inactive' from dual 
  union all
  select  'all' from dual
),
 clients (client, status, email, phone) as (
  select 001, 'active', 'yes', 'yes' from dual union all
  select 002, 'inactive', 'yes', 'yes' from dual union all
  select 003, 'inactive', 'yes', 'no' from dual union all
  select 004, 'deceased', 'no', 'no' from dual union all
  select 005, 'active', 'yes', 'no' from dual union all
  select 006, 'deceased', 'no', 'no' from dual union all
  select 007, 'active', 'yes', 'yes' from dual union all
  select 008, 'inactive', 'no', 'yes' from dual union all
  select 009, 'active', 'no', 'no' from dual union all
  select 010, 'inactive', 'yes', 'yes' from dual
)
SELECT  stat.status,  EMAIL, PHONE, COUNT(*) TOTAL
FROM CLIENTS, stat
where stat.status = clients.status or stat.status = 'all'
GROUP BY stat.status, EMAIL, PHONE
ORDER BY 1 DESC, 2 DESC, 3 DESC

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191265

Yout first query doesn't need the case expression, you can just filter on the single status you want; and then union with the second query:

-- CTE for sample data
with clients (client, status, email, phone) as (
  select 001, 'active', 'yes', 'yes' from dual union all
  select 002, 'inactive', 'yes', 'yes' from dual union all
  select 003, 'inactive', 'yes', 'no' from dual union all
  select 004, 'deceased', 'no', 'no' from dual union all
  select 005, 'active', 'yes', 'no' from dual union all
  select 006, 'deceased', 'no', 'no' from dual union all
  select 007, 'active', 'yes', 'yes' from dual union all
  select 008, 'inactive', 'no', 'yes' from dual union all
  select 009, 'active', 'no', 'no' from dual union all
  select 010, 'inactive', 'yes', 'yes' from dual
)
-- actual query
SELECT STATUS, EMAIL, PHONE, COUNT(*) TOTAL
FROM CLIENTS
WHERE STATUS = 'inactive'
GROUP BY STATUS, EMAIL, PHONE
UNION ALL
SELECT 'all' AS STATUS, EMAIL, PHONE, COUNT(*) TOTAL
FROM CLIENTS
GROUP BY EMAIL, PHONE
ORDER BY 1 DESC, 2 DESC, 3 DESC;

STATUS   EMAIL PHONE      TOTAL
-------- ----- ----- ----------
inactive yes   yes            2
inactive yes   no             1
inactive no    yes            1
all      yes   yes            4
all      yes   no             2
all      no    yes            1
all      no    no             3

Just for fun, you could do this while only hitting the table once, by using conditional aggregation:

select email, phone,
  count(case when status = 'inactive' then client end) as inactive,
  count(*) as total
from clients
group by email, phone
order by 1 desc, 2 desc;

EMAIL PHONE   INACTIVE      TOTAL
----- ----- ---------- ----------
yes   yes            2          4
yes   no             1          2
no    yes            1          1
no    no             0          3

and then unpivoting:

select status, email, phone, total
from (
  select email, phone,
    count(case when status = 'inactive' then client end) as inactive,
    count(*) as total
  from clients
  group by email, phone
)
unpivot (total for status in (inactive as 'inactive', total as 'all'))
where total > 0
order by 1 desc, 2 desc, 3 desc;

STATUS   EMAIL PHONE      TOTAL
-------- ----- ----- ----------
inactive yes   yes            2
inactive yes   no             1
inactive no    yes            1
all      yes   yes            4
all      yes   no             2
all      no    yes            1
all      no    no             3

but unless you have a lot of data in your real scenario you probably don't need to make it that complicated.

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

Try below with Union

SELECT STATUS, EMAIL, PHONE,COUNT(CLIENT) TOTAL
from CLIENTS where status='inactive'
group by STATUS,EMAIL, PHONE
union
SELECT 'All' as STATUS, EMAIL, PHONE,COUNT(CLIENT) TOTAL
from CLIENTS
group by EMAIL, PHONE

Upvotes: 2

Related Questions