Can we use aggregate function on both sides of having-CodePudding

Pretty simple question - can we run something like the following in sql, if not what's an alternative :

Edit #1 fixed question

The following query should answer : Give me all names and salaries by month for folks whose salary is greater than average of all salaries(not greater than avg. salary by month)

Select name, month, salary From table Group by 1,2 Having sum(salary) > avg(salary)

CodePudding user response：

You want two different aggregations:

The sum per name and month
The average per month

One option is using two CTEs:

with m as (select month, avg(salary) as sal from mytable group by month)
, nm as (select name, month, sum(salary) as sal from mytable group by name, month)
select m.month, m.sal as avgsal, nm.name, nm.sal
from nm
join m on m.month = nm.month
where nm.sal > m.sal
order by m.month, nm.name

CodePudding user response：

The following might do what you want.

WITH
TABLE_GROUPED AS (
  SELECT name, month, avg(salary) avg_salary, sum(salary) sum_salary
  FROM YOUR_TABLE
  GROUP BY 1, 2
)
SELECT *
FROM YOUR_TABLE
WHERE sum_salary > avg_salary

However, I am not sure why would you want to do that. Considering that the salary is positive, the only filtered grouped rows would be the ones that contain only one element. If the group has more than one element, the sum will always be higher than the mean.