Compare aggregated value for groups to the aggregated value for the entire table

sql group rows with same value
sql sum group by multiple columns
sql group by multiple columns
sql aggregate functions
sql query to sum two columns values based on it's record
group by having order by in sql
sum of grouped count in sql query
group by order by

Let's say I have one table that has E_name, DepartmentName, Salary

E_name  DepartmentName  Salary
AAA     D1              1000
BBB     D1              1400
CCC     D2              1320
DDD     D2              1100
EEE     D3              900
FFF     D3              1200

how to show DepartmentName where their employees' average salary is bigger than the average of all employees

You may use GROUP BY with a HAVING clause

select DepartmentName  
  from employees
 group by DepartmentName
 having avg(Salary) > ( select avg(salary) from employees );

SQL Server SUM() Function By Practical Examples, Which aggregate function is used to compute a total of a value expression? Group by statement is used to group each value of particular columns and then calculate aggregated values. i.e.; when group by COLUMN_NAME is used, it first finds all the DISTINCT values from the column of the table. Then it finds the aggregate values based on this grouped column/s. If we need to compare the value of this aggregated value, we

I'd join between an aggregate query that's grouped by department and an aggregate query that isn't grouped:

SELECT DepartmentName, AvgDepartmentSalary
FROM   (SELECT   DepartmentName, AVG(Salary) AS AvgDepartmentSalary
        FROM     employees
        GROUP BY DepartmentName) g
JOIN   (SELECT AVG(Salary) AS AverageSalary
        FROM   employee) u ON AvgDepartmentSalary > AverageSalary

Difference Between Aggregate and Average, Learn how to group results using GROUP BY; Use aggregate functions to perform calculations Rather than returning every row in a table, when values are grouped, only the unique combinations are returned. But there is a key difference. Aggregate values for all rows. Be sure the table you want to aggregate is already present in the Diagram pane. Right-click the background of the Diagram pane, then choose Group By from the shortcut menu. The Query and View Designer adds a Group By column to the grid in the Criteria pane. Add the column you want to aggregate to the Criteria pane.

 select dept_name,sal 
 group by dept_name 
 having sal > avg(sal);`

Did you mean this ? as this says all the dept groups having any of the salary > avg of all the salaries of that dept give that result.

GROUP BY and FILTER, Learn how to group results using GROUP BY; Use aggregate functions to perform calculations Rather than returning every row in a table, when values are grouped, only the unique combinations are returned. But there is a key difference. If you select SALARY, and GROUP BY SALARY/1000 you will see one sample salary from the matching group. The GROUP BY clause is normally used along with five built-in, or "aggregate" functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for

How to Group and Summarize your Results (simple explanation , a single value by grouping the rows according to the values in one or more columns. Power Query has two types of Group By operations: aggregate a column  Joe Celko, in Joe Celko's SQL for Smarties (Fifth Edition), 2015. Simple aggregate functions first construct a set of values as defined by the parameter. The parameter is usually a single column name, but it can be an expression with scalar functions and calculations.

Summarize Your SQL Results with the GROUP BY clause, In the Group By grid column, select the appropriate aggregate function, such as: Sum, Avg, Min, Max, Count. If you want to aggregate only unique  Aggregate functions available in Tableau. Aggregations and floating-point arithmetic: The results of some aggregations may not always be exactly as expected. For example, you may find that the Sum function returns a value such as -1.42e-14 for a column of numbers that you know should sum to exactly 0.

Group rows in a table (Power Query) - Excel, Cached aggregated data uses a fraction of the resources required for detail in the Manage aggregations dialog offers the following values: Queries that aggregate metrics from the Import-mode Sales Agg table, and group  For example, in a bar chart, an aggregated measure with the ForAll context uses the same aggregated value (calculated for the entire data item) for each bar in the chart. By using the ForAll and ByGroup contexts together, you can create measures that compare the local value to the global value.

Comments
  • What is your rdbms? Sql Server, postgres, oracle?
  • Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
  • You should really attempt to solve these exercises yourself. For instance, you might start with calculating the average salary over all employees.
  • i'm using SQL server
  • Are you familiar with GROUP BY?
  • You took it as a whole > for all depts. was that the query ohh :( i did for each dept.
  • That's was helpful Thank You, for another situation can I use a subquery with WHERE clause
  • @OmarMohamed you're welcome. Of course you can, but it's important to detect the right column to be bound for subqueries. By the way having is very similar to where. i.e. where for aggregations
  • @Omar Mohamed Could you please upvote my answer too it was just i compared within the dept but the only diff is why here subquery is used just get avg of all sal of emp in the whole table not group this is the thing i wanted from you to be cleared
  • Did not get why you did like this ?
  • is that code will compare the avg salary of the employees within a dept to the avg salary of all employees?
  • see in this case it is doing like suppose i have sal 1000,2000 so the avg is 3000/2=1500 my query will work like 1000>=1000(say equal to as well suppose),display else like in this case 1000>=2000, no so itll not display. This is for a particular dept