0

I am trying to work out a query for a transaction table with data as shown below:

Dept Employee TransactionDate Event
dept1 emp1 2022-05-20 abgd
dept1 emp1 2022-05-20 ggg
dept1 emp1 2022-05-20 hdfh
dept2 emp2 2022-01-26 3fdfds
dept2 emp2 2022-01-26 dsfsd
dept2 emp2 2022-01-26 554fsds
dept2 emp2 2022-01-26 gg32
dept2 emp2 2022-01-26 fd4gfg

I would like to list the count the no. of times the Dept+Employee+TransactionDate is repeated for each event as shown below:

Dept Employee TransactionDate Event count
dept1 emp1 2022-05-20 abgd 3
dept1 emp1 2022-05-20 ggg 3
dept1 emp1 2022-05-20 hdfh 3
dept2 emp2 2022-01-26 3fdfds 5
dept2 emp2 2022-01-26 dsfsd 5
dept2 emp2 2022-01-26 554fsds 5
dept2 emp2 2022-01-26 gg32 5
dept2 emp2 2022-01-26 fd4gfg 5

I am looking a way to get the expected view. If it's possible with a single sql query?

Any pointers will be appreciated.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
tintin
  • 5,676
  • 15
  • 68
  • 97

1 Answers1

2

Use the COUNT analytic function:

SELECT t.*,
       COUNT(*) OVER (PARTITION BY Dept, Employee, TransactionDate) AS cnt
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (Dept, Employee, TransactionDate, Event) AS
SELECT 'dept1', 'emp1', DATE '2022-05-20', 'abgd' FROM DUAL UNION ALL
SELECT 'dept1', 'emp1', DATE '2022-05-20', 'ggg' FROM DUAL UNION ALL
SELECT 'dept1', 'emp1', DATE '2022-05-20', 'hdfh' FROM DUAL UNION ALL
SELECT 'dept2', 'emp2', DATE '2022-01-26', '3fdfds' FROM DUAL UNION ALL
SELECT 'dept2', 'emp2', DATE '2022-01-26', 'dsfsd' FROM DUAL UNION ALL
SELECT 'dept2', 'emp2', DATE '2022-01-26', '554fsds' FROM DUAL UNION ALL
SELECT 'dept2', 'emp2', DATE '2022-01-26', 'gg32' FROM DUAL UNION ALL
SELECT 'dept2', 'emp2', DATE '2022-01-26', 'fd4gfg' FROM DUAL;

Outputs:

DEPT EMPLOYEE TRANSACTIONDATE EVENT CNT
dept1 emp1 2022-05-20 00:00:00 abgd 3
dept1 emp1 2022-05-20 00:00:00 hdfh 3
dept1 emp1 2022-05-20 00:00:00 ggg 3
dept2 emp2 2022-01-26 00:00:00 gg32 5
dept2 emp2 2022-01-26 00:00:00 554fsds 5
dept2 emp2 2022-01-26 00:00:00 dsfsd 5
dept2 emp2 2022-01-26 00:00:00 fd4gfg 5
dept2 emp2 2022-01-26 00:00:00 3fdfds 5

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117