CODEWITHSUNDEEP
sqlpostgresql
asukagr
asukagr

Reputation: 55

Complex select on a single column

I have this table

create table customers(id int, cust text, email id, cust_grp int);

and I use the following select query to get 3 results in 3 different conditions

select count(*) as cust_with_email 
from customers where email<>'' and cust_grp=101;

result
--------
199

select count(*)  as cust_without_email  
from customers where email='' and cust_grp=101;
result
--------
3370

select count(*)  as cust_total 
from customers where  cust_grp=101;
result
--------
3569

But now I need to combine these three queries into a single select and the expected output is:

 custemaildet
---------------------
3569|199|3370

Upvotes: 2

Views: 75

Answers (3)

Vivek S.
Vivek S.

Reputation: 21905

You can use case when to filter email column and concatenate the result using | symbol

SELECT count(*) || '|' || count(CASE 
            WHEN email <> ''
                THEN email
            END) || '|' || count(CASE 
            WHEN email = ''
                THEN email
            END) custemaildet
FROM customers 
WHERE cust_grp= 101

Upvotes: 1

pstef
pstef

Reputation: 91

Try this:

SELECT count(*), count(email = '' OR NULL), count(email <> '' OR NULL)
FROM customers
WHERE cust_grp = 101;

Or, In PG 9.4+:

SELECT
    count(*),
    count(*) FILTER (WHERE email = ''),
    count(*) FILTER (WHERE email <> '')
FROM customers
WHERE cust_grp = 101;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use conditional aggregation:

select count(*)  as cust_total,
       sum(case when email = '' then 1 else 0 end) as cust_without_email ,
       sum(case when email <> '' then 1 else 0 end)  as cust_with_email    
from customers
where cust_grp = 101

If you want one column, then you can concat them together:

select concat(count(*), '|',  
              sum(case when email = '' then 1 else 0 end), '|', 
              sum(case when email <> '' then 1 else 0 end)
             )    
from customers
where cust_grp = 101

Upvotes: 0

Related Questions