CODEWITHSUNDEEP
sqloracle-database
Nick Scotney
Nick Scotney

Reputation: 259

ORACLE : Using RANK() with Count(condition)

I'm currently trying to write an Oracle query which gets the number of allocation, per hour, per user. I've come up with the following, which does just this.

SELECT DISTINCT
    USER_CODE, 
    TO_CHAR(TAUDIT.ENTERED, 'HH24') Hour, 
    COUNT(TO_CHAR(TAUDIT.ENTERED, 'HH24')) OVER (PARTITION BY TO_CHAR(TAUDIT.ENTERED, 'HH24'), USER_CODE ORDER BY TO_CHAR(TAUDIT.ENTERED, 'HH24')) Allo_Count
FROM TAUDIT
WHERE TAUDIT.ACTION LIKE 'Job Allocated event: All%'
AND TO_CHAR(TAUDIT.ENTERED, 'DD.MM.YY') = TO_CHAR(( SYSDATE - INTERVAL '1' day), 'DD.MM.YY')

This produces a list in the following format:

User_Code    Hour    Allo_Count
---------    ----    ----------
CE           09      1
TB           09      3
CE           10      1
TB           10      4

What I'm now trying to achieve is a the ability to rank the result by Allo_Count, so that I can then select only the highest count for each hour. As I have little experience with oracle, I've been going over countless posts as well as the Oracle online documentation to try to understand and work this out, however I don't seem to be able to get around the issue of using the COUNT() within the order by ranking.

I added in the following line, to the query to try and get the RANK in the output

Rank() OVER (Partition by TO_CHAR(TAUDIT.ENTERED, 'HH24') Order by COUNT(TO_CHAR(TAUDIT.ENTERED, 'HH24'))DESC) as 'Allo_Rank'

However this gives me a "Not a single-group group function" error, so clearly this isn't the way. From articles and posts I've read I'm pretty sure once I've ironed this out, I can surround the query with 

SELECT * FROM (QUERY) WHERE Allo_Rank = 1

But it's just getting over this hurdle in the first place. Any advice or tips on how to finish this query are appreciated, because right now, my head is fried!

Upvotes: 0

Views: 3423

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Why are you using select distinct? Use group by. And don't do date comparisons using strings. So, this appears to be what you want:

SELECT USER_CODE, TO_CHAR(t.ENTERED, 'HH24') Hour, 
       COUNT(*) as Allo_Count
FROM TAUDIT t
WHERE t.ACTION LIKE 'Job Allocated event: All%' AND
      TRUNC(t.ENTERED) = TRUNC(SYSDATE - INTERVAL '1' day)
GROUP BY USER_CODE, TO_CHAR(t.ENTERED, 'HH24');

For the ranking:

SELECT USER_CODE, TO_CHAR(t.ENTERED, 'HH24') as Hour, 
       COUNT(*) as Allo_Count,
       RANK() OVER (PARTITION BY TO_CHAR(t.ENTERED, 'HH24') ORDER BY COUNT(*)) as ranking
FROM TAUDIT t
WHERE t.ACTION LIKE 'Job Allocated event: All%' AND
      TRUNC(t.ENTERED) = TRUNC(SYSDATE - INTERVAL '1' day)
GROUP BY USER_CODE, TO_CHAR(t.ENTERED, 'HH24');

Upvotes: 1

Nick Scotney
Nick Scotney

Reputation: 259

Based on Gordon's answer, I've solved the issue including "Partition By" in the query. Below is the updated query:

SELECT USER_CODE, TO_CHAR(t.ENTERED, 'HH24') Hour, 
   COUNT(*) as Allo_Count,
RANK() OVER (PARTITION BY TO_CHAR(t.ENTERED, 'HH24') ORDER BY COUNT(*)) as ranking
FROM TAUDIT t
WHERE t.ACTION LIKE 'Job Allocated event: All%' AND
  TRUNC(t.ENTERED) = TRUNC(SYSDATE - INTERVAL '1' day)
GROUP BY USER_CODE, TO_CHAR(t.ENTERED, 'HH24')

Which provides the following result set

User    Hour    Count    Ranking
----    ----    -----    -------
CE      07       1        1
TB      09       1        1
CE      09       3        2
TB      10       1        1
CE      10       4        2

Hopefully that's the right way to go about it, but please feel free to let me know if not

Upvotes: 0

Related Questions