CODEWITHSUNDEEP
sqlpostgresqlview
John Kouraklis
John Kouraklis

Reputation: 686

Can't Generate the SQL Query

I've got one table called Calls with the following fields.

I want to create a view that groups the calls by Week and shows the following columns:

I start with this definition:

CREATE VIEW TotalView AS select
  Call.Week,
  Count(ID) as TotalCalls
From
  Call
GROUP BY
  Call.Week;

The Weeks and the TotalCalls show correctly. Then, I try to calculate the calls with satisfaction score with less that 3 but the results are wrong. I enter this statement

...
select
  Call.Week,
  Count(ID) as TotalCalls,
  Count(CallsLess) as CallsLess3

From
  Call,
  (select ID as CallsLess from Call where SatisfactionRate<3)

GROUP BY
  Call.Week

...but the result table is totally wrong and now the TotalCalls column is even wrong.

  1. Can you please advise how I should write this query?
  2. My data for a specific month cover 5 weeks. Can I, also, group weeks 4 and 5 in one so I end up with four rows in the final view?

Upvotes: 0

Views: 47

Answers (3)

forpas
forpas

Reputation: 164069

With conditional aggregation:

select
  week,
  count(*) totalcalls,
  100.0 * sum(case when exittime is null then 1 else 0 end) / count(*) abandonrate,
  sum(case when satisfactionrate < 3 then 1 else 0 end) callsless3
from call
group by date_trunc('month', date)::DATE, week

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

Use "conditional aggregation" by placing case expressions inside the aggregate functions.

CREATE VIEW TotalView AS select
  Call.Week,
  Count(ID) as TotalCalls,
  (Count( case when exit_time is null then 1 end ) * 100.0 ) / count(*) as SatisfactionScore
From
  Call
GROUP BY
  Call.Week;

To implement non-standard calendars probably requires that you build a calendar table and use that to inform your queries how you have defined weeks to be treated. I have always preferred a week to remain 7 days in leng, anything else is confusing imho.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Postgres supports the filter clause, which makes conditional aggregation almost friendly:

select c.Week as call_week,
       count(*) as total_calls,
       count(*) filter (where c.exittime is null) as abandoned_calls,
       avg( (c.exttime is null)::int ) as abandon_rate,
       count(*) filter (where c.satisfaction < 3) as low_satisfaction
from call c
group by c.Week;

Upvotes: 1

Related Questions