using aggregate function with SQL having clause

Question

can anyone tell me, why is this query giving an error

Write a query to find the maximum total earnings (earnings) for all employees as well as the total number of employees (num_employees) who have maximum total earnings. total earnings = months worked *salary We define an employee's total earnings to be their monthly (salary×months) worked

SELECT
 (salary*months) as earnings 
 ,count(*) as num_employees
FROM employee
group by salary*months  
having max(salary * months)=salary*months

Answer 1

there is a logic issue in your code. you see, the HAVING clause is used to perform filtering on the results of the grouped data, so using MAX in the having clause might not get you the results you expect.

if you want to find the maximum total earnings and the number of employees with that maximum total earnings, you can use a subquery to first find the maximum total earnings and then join it back to the original table like this:

SELECT
  e.salary * e.months as earnings,
  COUNT(*) as num_employees
FROM employee e
JOIN (
  SELECT MAX(salary * months) as max_earnings
  FROM employee
) max_earnings_query ON e.salary * e.months = max_earnings_query.max_earnings
GROUP BY e.salary * e.months;

Answer 2

Order by earnings descending. Pick the first row only, the one with the highest earnings:

ANSI/ISO SQL:

SELECT
 (salary*months) as earnings 
 ,count(*) as num_employees
FROM employee
group by salary*months  
order by earnings desc
fetch first 1 row only

Instead of FETCH FIRST, some non-standard alternatives are LIMIT 1 and SELECT TOP 1.