MySql Query example using Aggregate function



  • Doing some practice example queries in MySQL and I am stuck on one.

    Here is the relevant table:

    create table instructor (
        ID          varchar(5),
        name        varchar(20) not null,
        dept_name   varchar(20),
        salary      numeric(8,2) check (salary > 29000),
        primary key (ID),
        foreign key (dept_name) references department(dept_name)
            on delete set null
    ) ENGINE = INNODB;
    

    The question is: for each instructor, get their name, their salary and the number of instructors who earn more than they do.

    Naturally, my mind goes to using the COUNT() aggregate function. I have come up with this answer (I know I added the ID column but it doesn't matter):

    SELECT DISTINCT T.ID, T.name, T.salary, COUNT(S.ID) AS num
    FROM instructor AS T, instructor AS S
    WHERE S.salary>T.salary
    GROUP BY T.ID;
    

    But the issue here is that it leaves out the row for the instructor who earns the highest salary, as he is excluded in the WHERE clause.

    I thought about making the WHERE clause greater than OR equal to and just decrementing each number but is that even possible?

    I also thought that something like this would work:

    Select DISTINCT T.ID, T.name, T.salary, S.salary as salary2, count(S.salary) as num
    FROM instructor as T, instructor as S
    WHERE S.salary>T.salary
    group by T.ID
    having S.salary>T.salary;
    

    I've gotta be missing something easy here.



  • Use a subquery instead. It will also obviate the GROUP BY, etc.

    Select  T.ID, T.name, T.salary, T.salary,
            ( SELECT count(*)
                   FROM instructor AS S 
                   WHERE S.salary > T.salary
            ) as num
        FROM  instructor as T;
    

    Note

    COUNT(*) is the usual way to say it; COUNT(X) counts how many Xs are not-NULL.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2