Can't Generate the SQL Query

can definition
can synonym
can noun meaning
can bus
can band
can airport
can verb
can network

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

  • ID
  • EntryTime
  • ExitTime (This can be NULL which indicates an abandon call)
  • Date
  • Week (of the month based on date)
  • SatisfactionScore (1-5)

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

  • Week
  • Total Calls Per Week
  • Abandon Rate (Division of Total Calls by the number of calls that ExitTime is NULL) per Week
  • Calls With Satisfaction Score Less than 3 per Week

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?

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.

Can, Can may refer to: Contents. 1 Containers; 2 Music; 3 Other; 4 See also. Containers[edit]. Aluminum can � Drink can � Oil can � Steel and tin cans � Trash can� Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.

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; 

Can, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most� Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the

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

Can (band), 1.4Used to indicate that something is typically the case. 'antique clocks can seem out of place in modern homes'. More example sentences. can definition: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more.

The Official CAN / Spoon Records Website, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth). 110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.

Can, can noun [C] (CONTAINER) � can verb [T] (CONTAIN) � can modal verb (ABLE) � can modal verb (PERMIT) � can modal verb (BE POSSIBLE) � can� Can (stylised as CAN) was a German experimental rock band formed in Cologne in 1968 by the core quartet of Holger Czukay (bass, tape editing), Irmin Schmidt (keyboards), Michael Karoli (guitar), and Jaki Liebezeit (drums).

can, Word forms � 1. countable noun A can is a metal container in which something such as food, drink, or paint is put. � 2. verb [usually passive] When food or drink is� HANGZHOU, China, Aug. 04, 2020 (GLOBE NEWSWIRE) -- Canaan Inc. (NASDAQ: CAN) ("Canaan" or the "Company"), a leading high-performance computing solutions provider, today announced that the terms of

Comments
  • Thank you all so much for the comments!!! Really couldn't decide which answer to choose.
  • if the column week has values like 1,2,3,4, if you only group by week then you end up grouping together all the weeks, say number 1, of all the months.