Oracle SQL select rows that are not in GROUP BY clause

user_12103537 0
Viewed 405

Assuming I have a table with columns first_name, second_name and score. I want to list all columns and the number of rows with the same value in score column for each of them.

If I have to list only score and rows with the same score, I would do something like this:

SELECT score, COUNT(*) FROM tab GROUP BY score;

In SELECT you can't use columns that are not in GROUP BY clause. So what should I do if I want to list not only score, but also first_name and second_name?

group-by oracle sql
2 Answers

You can use analytical functions:

SELECT first_name, second_name, score, COUNT(*)  over (partition by score) FROM tab ;
user_143837680 
SELECT t.first_name, t.second_name, t.score, sc.score_count
from tab t
, (select score, COUNT(*) score_count FROM tab GROUP BY score ) sc
where t.score = sc.score
user_3187490
Related