View All Columns Where Name Repeats

user_10358906 0
Viewed 25

I've been asked to provide a list of all employees who are scheduled to take >6 trips. I need to have all the columns of their record showing for each trip.

I'm very new to SQL and don't have the logic down. So far I've been able to get a count:

SELECT SURNAME, GIVEN_NAME, COUNT(SURNAME)    
from EP_EVENT    
GROUP BY SURNAME, GIVEN_NAME HAVING COUNT(SURNAME) > 6;

But I need to see * not just SURNAME and GIVEN_NAME, none of the other columns contain the same information.

Help

oracle
1 Answers

You could use windowed COUNT:

WITH cte AS (
   SELECT e.*, COUNT(*) OVER (PARTITION BY SURNAME, GIVEN_NAME) AS cnt
   FROM  EP_EVENT e
)
SELECT *
FROM cte
WHERE cnt > 6;
user_50708790
Related