CODEWITHSUNDEEP
sqlexcelamazon-web-services
Ketih Carpenter
Ketih Carpenter

Reputation: 85

SQL Count when criteria is met

Apologies if this seems like a stupid question but I am new enough to SQL, I am running ODBC and using power query in Excel. I am looking to have for example the following as columns (this is pulled from AWS CTR). Queue Call Arrival date Calls offered Calls Answered Calls Answered (in specified time)

I am finding it difficult to pull the calls answered as a column, below is the current statement I am running and getting errors, If I remove the "calls Answered" statement the query will work as expected. Any suggestions for this will be greatly appreciated.

SELECT queue.name as "queue", cast(substring(connectedtosystemtimestamp,1,10) as date) as "call arrival date", count(contactid) as "Calls Offered", count(case when(cast(queue.duration as integer)<30) then 1 end) as "answered below 30 sec", SUM(CASE initiationmethod=’INBOUND’, name IS NOT NULL AND agent IS NOT NULL) AS "Calls answered"
FROM Catalog.db.table
WHERE initiationmethod = 'INBOUND' AND queue is not null
GROUP BY queue.name, cast(substring(connectedtosystemtimestamp,1,10) as date)

Upvotes: 0

Views: 54

Answers (1)

GMB
GMB

Reputation: 222582

This is not valid SQL:

SUM(CASE initiationmethod=’INBOUND’, name IS NOT NULL AND agent IS NOT NULL) AS "Calls answered"

Presumably, you want:

SUM(CASE 
    WHEN initiationmethod = 'INBOUND' 
        AND name IS NOT NULL 
        AND agent IS NOT NULL 
    THEN 1 
    ELSE 0 
END) AS "Calls answered"

Upvotes: 2

Related Questions