CODEWITHSUNDEEP
sql
user15195885
user15195885

Reputation:

Combine two SQL queries into a single SQL query statement

I was using this query to get count of users whose date of birth lies between the given date range.

select 
    count(distinct first_name) 
from 
    quser q 
inner join 
    other_details o on o.country = q.country_of_birth 
where 
    date_of_birth between '2020-02-01' and '2020-03-01' 
    and email is not null;

Output

 count
-------
    21
(1 row)

Using the same query, but this time, users not fall on the given range added not before between.

select 
    count(distinct first_name) 
from 
    quser q 
inner join 
    other_details o on o.country = q.country_of_birth 
where 
    date_of_birth not between '2020-02-01' and '2020-03-01' 
    and email is not null;

Output

 count
-------
   498
(1 row)

Is there any way to combine the query and produce a single output as,

 count no  count yes
---------  -------
   498        21
(1 row)

Upvotes: 1

Views: 56

Answers (1)

forpas
forpas

Reputation: 164184

Use conditional aggregation:

select count(distinct case when date_of_birth between '2020-02-01' and '2020-03-01' then first_name end) count_yes,
       count(distinct case when date_of_birth not between '2020-02-01' and '2020-03-01' then first_name end) count_no
from quser q inner join other_details o 
on o.country = q.country_of_birth 
where email is not null;

Upvotes: 2

Related Questions