Question 113360 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Find the maximum in the table and then display the SQL group and the count of maximum in each and every SQL group respectively

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.

Tags:

2

Answers


  1. 0

    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

    Edit. The answer works in Oracle as well, but the keyword as after the subquery needs to be removed

    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

    Login or Signup to reply.
  2. 0

    Using sum:

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

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search