How to sort this query from monday first to friday last?

user_14520451 0
Viewed 43

I want to order this query and get the ticket amount for each day this week with a result like this:

WEEK_DAY      CREATED_TICKETS
MONDAY        3
TUESDAY       5
WEDNESDAY     0
FRIDAY        2

SELECT 
  TO_CHAR(TRUNC(CREATED_AT), 'DAY') AS WEEK_DAY,
  COUNT(ID) as CREATED_TICKETS
FROM
  FRESHDESK_API
WHERE
  TO_CHAR(TRUNC(CREATED_AT), 'IW') = TO_CHAR(TRUNC(SYSDATE), 'IW')
GROUP BY
  TO_CHAR(TRUNC(CREATED_AT), 'DAY')
ORDER BY
  TO_CHAR(TRUNC(CREATED_AT), 'DAY') ASC
oracle-apex oracle sql
1 Answers

I would recommend:

select to_char(created_at, 'day') as week_day, count(*) as created_tickets
from freshdesk_api
where created_at >= trunc(sysdate, 'iw') and created_at < trunc(sysdate, 'iw') + 7
group by to_char(created_at, 'day')
order by min(created_at)

The trick is to use an aggregate function in the order by clause that applies on the date column.

Other changes to your query:

  • I optimized the where clause so no date function is applied on the column being filtered; this is more efficient (one says that the predicate is SARGable)

  • presumably something called id is not nullable, so count(*) is equivalent to count(id) (and more efficient, because the database does not need to null-check each value)

  • no need to nest trunc() and to_char(); to_char() is sufficient here, since you only care about the day part

user_106767160
Related