CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
tychopycho
tychopycho

Reputation: 41

How to count total amount of pending tickets monday in oracle-sql?

I want to count the total amount of pending tickets for monday. I don't want the amount of tickets that were moved to pending on monday but the total amount.

table with sample data:

enter image description here


Expected result:
Total pending tickets monday

Upvotes: 0

Views: 373

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If each row represents a ticket, then you can get the number that are pending on a particular day using:

select count(*)
from t
where updated_at < :date and status = 'pending';

Note: This is not going to count "pending" statuses that were changed to another state. This data does not have enough information to answer that. And your question has not explained the state changes.

I would actually suggest that you ask a new question with more comprehensive information about what the states are, how they change as well as sample data and desired results.

Upvotes: 1

Popeye
Popeye

Reputation: 35910

I am assuming that the tickets which are not closed are pending. You can use the GROUP BY and HAVING as follows:

SELECT COUNT(1) AS PENDING FROM YOUR_TABLE T
 WHERE T.CREATED_AT <= <<YOUR_DATE>>
 GROUP BY T.TICKET_ID
HAVING SUM(CASE WHEN T.STATUS = 'closed' THEN 1 ELSE 0 END) = 0

Upvotes: 0

Related Questions