CODEWITHSUNDEEP
sqlamazon-redshift
Thomas Stevens
Thomas Stevens

Reputation: 33

Getting The Results of 2 Queries from 1 Table with different Where conditions

I have 2 Queries that I would like to combine into one. It is from the same table but has one slight difference in the where statement.

I tried Union, Filter, and using multiple select statements within the select statement.

Query 1

Select date, count("speed of answer") as TotalCalls     
From five9_data.calllog     
Where Campaign IN ('FYF', 'SF') 
    and "Campaign Type" IN ('Inbound') 
    and "Time TO Abandon" is null     
Group By date     
Order By date Desc

Query 2

Select date, count("Time To Abandon") as AbandonCalls 
From five9_data.calllog 
Where Campaign IN ('FYF', 'SF') 
    and "Campaign Type" IN ('Inbound') 
    and "Time TO Abandon" > '0' 
Group By date 
Order By date Desc

I want the Date, Column with the results from Total Calls and the results from Abandon Calls in the results. If you notice the big difference is the 3rd condition in the where statement of both.

Upvotes: 1

Views: 67

Answers (2)

Thomas Stevens
Thomas Stevens

Reputation: 33

Select

date, sum( ("Time TO Abandon" is null)::int ) as TotalCalls,

   sum( ("Time TO Abandon" > 0)::int ) as AbandonCalls

From five9_data.calllog

Where Campaign IN ('FYF', 'SF') and "Campaign Type" IN ('Inbound')

Group By date

Order By date Desc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270703

I think you want conditional aggregation:

Select date, sum( ("Time TO Abandon" is null)::int ) as TotalCalls,
       sum( ("Time TO Abandon" > 0)::int ) as AbandonCalls
From five9_data.calllog
Where Campaign IN ('FYF', 'SF') and "Campaign Type" IN ('Inbound') and
Group By date
Order By date Desc;

Upvotes: 1

Related Questions