How to fix invalid use of group function error?-CodePudding

I have the following query which is giving the error in the title. But I'm not sure what is the reason for this or how to fix it?

I'm trying to find the ids of countries with the presidents with the maximum salary

select c.id from country as c
inner join president as p on c.pid = p.pid
join president as p2
on p.salary = max(p2.salary)

Tables: President(pID: int, salary: int)

country(cID : int, pID : int)

CodePudding user response：

You are using a group aggregation function max(p2.salary) without using any grouping. You need to do the grouping in a subquery.

To get the country that pays their president the most, use a query like this:

select c.cID, p.salary 
from country as c
inner join president as p on p.pID = c.pID
inner join (
    select max(salary) as maxsalary
    from president) as p2 on p.salary = p2.maxsalary

Note that if multiple countries pay the same as the maximum salary to their president, all these countries will be returned.

CodePudding user response：

To get the country which pays its president more than other countries pay their president...

SELECT
  *
FROM
  country    c
INNER JOIN
  president  p
    ON p.pID = c.pID
WHERE
  p.salary = (SELECT MAX(salary) FROM president)

Or...

SELECT
  *
FROM
  country    c
INNER JOIN
(
  SELECT
    *,
    RANK() OVER (ORDER BY salary DESC)  AS rn
  FROM
    president
)
  p
    ON p.pID = c.pID
WHERE
  p.rn = 1

In both cases, if multiple presidents are tied for having the highest salary, all of those presidents (and their countries) are returned.

CodePudding user response：

You are using max(p2.salary)in the ON clause of a join. This shows you misunderstand what a join does. The ON clause looks at two rows (the left and the right; in your case a country row and a president row) in order to decide whether to join them, i.e. keep the row consistng of the two or dismiss the pair. While there is a salary in the president row, this is just one value. max(salary) makes no sense here hence. You cannot use aggregation functions in the ON clause. (The same is true for the WHERE clause by the way.)

What you want is countries with presidents earning the maximum salary. So we select from countries where the president has the salary that is the maximum salary of all salaries. One way to translate this into SQL (quite literally):

select *
from country
where president_id in
(
  select president_id
  from president
  where salary = (select max(salary) from president)
);

Of course you can also join the tables. You would especially do that if you wanted to show the presidents, too:

select *
from country c
join president p on p.president_id = c.president_id
where p.salary = (select max(salary) from president);

Or you could tell the DBMS to select all countries with their presidents and salaries and then sift through this list in order to keep only those lines with the maximum salary:

select *
from
(
  select c.*, p.*, max(p.salary) over () as max_salary
  from country c
  join president p on p.president_id = c.president_id
)
where salary = max_salary;

There are still other ways to get the same result, e.g. using EXISTS instead of IN, or getting the maximum paid presidents by asking "WHERE NOT EXISTS a higher salary", or ranking the rows with RANK or DENSE_RANK, etc.

(I've named the president IDs president_id for readability. This also makes it easier to avoid or spot typos as you had in your original request. If you want to stick with your abbreviations, simply rename president_id to pid in all my queries :-)