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;
COUNT(*)is the usual way to say it;
COUNT(X)counts how many Xs are not-NULL.