Find the maximum value in the table and then display the SQL groups separately and the number of maximum values ​​in each SQL group

Question

ID  POSITION    EXPERIENCE  SALARY
1   top               90    1500
2   bottom           100    1500
3   top               90    750
4   left              90    1000
5   right            100    1300
6   top               90    1500
7   left              80    2000
8   top               80    1000
9   bottom           100    2000
10  left             100    2000

So, this is my table SERVICE, where (as we can see) the max experience is 100. I need to write a query to find the number of occurrences of 100 in experience for each group formed by position (left, right, top, bottom).

So I wrote:-

select position,count(*)
from service
group by position
having experience=(select max(experience) from service);

Expected output: -

POSITION  COUNT(*)
bottom         2 
left           1
right          1
top            0

but, It gave me an error: - "Not a GROUP BY expression"

My logic is that first I divide it into several groups, and then use the having clause to calculate the tuples in each group whose experience is equal to the maximum value. experience.

Answer 1

One way is to use a left join with a subquery, which only returns the maximum value. A case is required to return the group with an arbitrary maximum value.

SELECT s.position,
       sum(case when max_experience is null then 0 else 1 end ) as max_count
FROM service s
LEFT JOIN  ( select max(experience) as max_experience
             from service 
            ) as s1 ON  s.experience = s1.max_experience
group by s.position
order by max_count desc ;

https://dbfiddle.uk/-8pHZ8wm

To make it easier to understand, run the query below and you will find that max_experience is empty in every row in the service table except the value 100. Simply put, you only need to count the rows in the group with values ​​100 and 0 that have not yet reached the maximum experience value.

SELECT s.*,s1.*
FROM service s
LEFT JOIN  (select max(experience) as max_experience
             from service 
            ) as s1 ON  s.experience = s1.max_experience ;

https://dbfiddle.uk/al8YYLk9

edit. The answer also works in Oracle, but you need to remove the keyword as

after the subquery

SELECT s.position,
       sum(case when max_experience is null then 0 else 1 end ) as max_count
FROM service s
LEFT JOIN  ( select max(experience) as max_experience
             from service 
            )  s1 ON  s.experience = s1.max_experience
group by s.position
order by max_count desc ;

https://dbfiddle.uk/hhGB_xXx

Answer 2

Sum using :

select position, sum(experience = 100) from tbl group by position

See violin.