make in one query a count and max over partion by in SQL Oracle-CodePudding

In Oracle SQL I have a table with

userid	qualification	date
One	Qual1	01/01/2020
One	Qual2	01/01/2022
Two	Qual1	01/01/2021
Three	Qual2	01/01/2022

I want to have per user id:

the count of qualifications
the most recent qualification

So for this example I want:

userid	qualification	count
One	Qual2	2
Two	Qual1	1
Three	Qual2	1

I thought to use something like this:

select userid, 
    count(qualification)OVER (PARTITION BY userid) as count_qual,
    MAX(qualification) OVER (PARTITION BY userid ORDER BY date desc) as qual_id
from Qualificaitons

but it returns me two lines for userid One

CodePudding user response：

You can use two functions to compute the result you want. For example:

select userid, qualification, cnt
from (
  select t.*,
    count(*) over(partition by userid) as cnt,
    row_number() over(partition by userid order by date desc) as rn
  from Qualificaitons t
) x
where rn = 1

CodePudding user response：

You can use MAX(..) KEEP (DENSE_RANK ..) aggregation function:

SELECT userid,
       MAX(qualification) KEEP (DENSE_RANK LAST ORDER BY "DATE") AS qualification, 
       COUNT(qualification) AS count
FROM   qualifications
GROUP  BY userid;

Which, for the sample data:

CREATE TABLE qualifications (userid, qualification, "DATE") AS
SELECT 'One',   'Qual1', DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 'One',   'Qual2', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 'Two',   'Qual1', DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 'Three', 'Qual2', DATE '2022-01-01' FROM DUAL;

Outputs:

USERID QUALIFICATION COUNT

One Qual2 2

Three Qual2 1

Two Qual1 1

db<>fiddle here

CodePudding user response：

use first_value instead of max as agg function. read documentation here.

select userid, count(qualification)OVER (PARTITION BY userid) as count_qual, 
first_value(qualification) OVER (PARTITION BY userid ORDER BY date desc) as qual_id 
from Qualificaitons