Calculate Count Distinct without grouped columns in Oracle
You use COUNT(DISTINCT ..) expression without GROUP BY clause. If you need this value for each group (if there are duplicates in your output while removing COUNT() expression) you must add GROUP BY for all other fields in output list. Or you can use the window variant of COUNT() function which is scalar. - Akina