SQL query, using group functions

group functions in sql
sql aggregate functions
sql group by multiple columns
nested group functions in sql
group by having sql
group function in oracle
sql group by sum
sql group by order by
Select department_id, max(avg(salary)) 
From employees
Group by department_id

The above query gives the error

ORA-00937: not a single-group function

I want to display the average salary for each department and also the maximum average salary from the departments. There are multiple departments. I'm using oracle 11g.

If you want the department with the maximum average salary -- and want only one row in the case of ties, you can simply do:

select e.*
from (select department_id, avg(salary) as avg_salary
      from employees
      group by department_id
      order by avg(salary) desc
     ) e
where rownum = 1;

In Oracle 12C, you don't need a subquery. You can use fetch first 1 row only

Aggregating Data using Group Functions, SQL has numerous predefined aggregate functions that can be used to write queries to produce exactly this kind of information.The GROUP BY clause specifies� SQL GROUP Functions. Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT. SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

The problem with your logic is that you are asking Oracle to do two levels of aggregation in a single GROUP BY, and that isn't possible. As a workaround, we can try using ROW_NUMBER here:

WITH cte AS (
    SELECT department_id, AVG(salary) AS avg_salary,
        ROW_NUMBER() OVER (ORDER BY AVG(salary) DESC) rn
    FROM employees
    GROUP BY department_id
)

SELECT department_id, avg_salary
FROM cte
WHERE rn = 1;

If two departments might be tied for the highest average salary, then you may replace ROW_NUMBER above with either RANK or DENSE_RANK.

Using Group functions, SQL GROUP Functions. SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. For Example: If you want the number of employees in a particular department, the query would be: SQL DISTINCT(): This function is used to select the distinct rows. The SQL GROUP BY Statement. The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. GROUP BY Syntax

you can simply rewrite your query as follows. There are many ways to achieve this.

Select department_id, max(avg_Salary) from (
 select department_id, avg(salary) as avg_Salary
From employees
Group by department_id)
Group by department_id

SQL Group Functions, The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group. Important Points: GROUP BY clause is used with the SELECT statement. Using Group Functions. You can use AVG and SUM for numeric data. MIN and MAX can be applied on any data type including numbers, dates and strings. The order is defined accordingly. Group functions ignore the NULL values in the column. To enforce the group functions ti include the NULL value, use NVL function.

If you want to select BOTH average salary per department AND maximum average salary aming departments then you should try the following code:

with qr as (
    select department_id, avg(salary) as dept_avg_salary
      from employees 
     group by department_id
)
select qr.department_id
     , qr.dept_avg_salary
     , (select max(dept_avg_salary) from qr) as max_avg_salary
  from qr

I hope I helped!

SQL, The GROUP BY clause is a SQL command that is used to group rows that have Optionally it is used in conjunction with aggregate functions to produce The queries that contain the GROUP BY clause are called grouped� To do the real analysis, you can combine the GROUP BY clause with one of the aggregate functions (also called set functions) to get a quantitative picture of sales performance. For example, you can see which salesperson is selling more of the profitable high-ticket items by using the average (AVG) function as follows:

To get average salary for each department and also the maximum average salary from all departments use this query:

with sal as (
Select department_id,  avg(salary) avg_salary
From employees
Group by department_id)
select 
  DEPARTMENT_ID, AVG_SALARY,
  max(AVG_SALARY) over () MAX_AVG_SALARY
from sal 

The first subquery is a simple aggregation of the AVG_SALARY. In the second step you use analytic function to get the global maximum of the averages. You get the global maximum by ommiting the PARTITION BY clause using only OVER()

MySQL GROUP BY and HAVING Clause with Examples, Using GROUP BY clause with the COUNT() function example. The following query returns the number of customers in every city: SELECT city, COUNT (� GROUP BY enables you to use aggregate functions on groups of data returned from a query. FILTER is a modifier used on an aggregate function to limit the values used in an aggregation. All the columns in the select statement that aren’t aggregated should be specified in a GROUP BY clause in the query.

SQL Server GROUP BY, In this manner, they act as subtotals. General Syntax of an Aggregate Function. When using the aggregate function you can either compute the result on all values� One last thought on the subject because you ask "Can I group by in a SQL query with a window function?" Alex covers that the PARTITION BY is like a sub grouping within the Window Function. But to use a GROUP BY grouping with a Window function means that the GROUP BY result set would be evaluated PRIOR to the Window Function being evaluated.

How to Group and Summarize your Results (simple , Use GROUP BY to summarize query results. Use it 5 Typical SQL Syntax Mistakes Learn how to group results using GROUP BY; Use aggregate functions to� Summary: in this tutorial, you will learn about SQL window functions that solve complex query challenges in easy ways.. Introduction to SQL Window Functions. The aggregate functions perform calculations across a set of rows and return a single output row.

Summarize Your SQL Results with the GROUP BY clause, This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples. The SQL GROUP BY clause can be used in a SELECT statement to collect data across Example - Using GROUP BY with the SUM Function. Group By Clause The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions. Generally, these functions are aggregate functions such as min (),max (),avg (), count (), and sum () to combine into single or multiple columns.

Comments
  • If you would care to formulate your required functionality a bit more carefully, you would get less answers;)