Find the maximum in the table and then display the SQL group and the count of maximum in each and ev-CodePudding

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 maximum experience is 100. I need to write a query to find the number of occurences of 100 in experience in each and every group made through 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 gives me an error saying :- "not a GROUP BY expression"

My logic is that, first I am dividing it into groups and then using having clause I am counting those tuples in each group where the experience equals max. experience.

CodePudding user response：

One way is using left join with a subquery wich will return only the maximum value . The case is needed to return the groups which have any max 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 more easy to understand run below query and you will the that max_experience is null in every row of service table except for the value 100. In simple words you need to count only the rows with value 100 and 0 for the groups which hasn't the the max 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

CodePudding user response：

Using sum:

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

See fiddle.