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

Answers


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

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

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;

Need Your Help

Why is the `Sized` bound necessary in this trait?

rust traits

I have a trait with two associated functions:

Scheme function

function scheme

I am trying to interpret what this scheme function does: