w3resource

PostgreSQL Aggregate Functions and Group By: Get the average salary and number of employees working in a particular designation


5. Write a query to get the average salary and number of employees working in the department which ID is 90.

Sample Solution:

Code:

-- Calculate the average salary and count of employees in the department with ID 90
SELECT AVG(salary),count(*) 
FROM employees 
WHERE department_id = 90;

Explanation:

  • This SQL query is designed to calculate the average salary and count of employees within the department with ID 90.
  • The AVG() function is an aggregate function in SQL that calculates the average value in a column.
  • salary is presumably a column in the employees table that holds the salary information for each employee.
  • count(*) is used to count the number of rows returned by the query, effectively counting the number of employees in the department with ID 90.
  • department_id is likely a column in the employees table that identifies the department each employee belongs to.
  • The WHERE clause filters the rows to include only those where the department_id is equal to 90, representing the department with ID 90.
  • The query calculates the average salary and counts the number of employees for all rows in the employees table that belong to the department with ID 90.

Sample table: employees


Output:

pg_exercises=# SELECT AVG(salary),count(*)
pg_exercises-# FROM employees
pg_exercises-# WHERE department_id = 90;
        avg         | count
--------------------+-------
 19363.333333333333 |     3
(1 row)

Relational Algebra Expression:

Relational Algebra Expression: Get the average salary and number of    employees working in a particular designation.

Relational Algebra Tree:

Relational Algebra Tree: Get the average salary and number of    employees working in a particular designation.

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to get the maximum salary of an employee working as a Programmer.
Next: WWrite a query to get the highest, lowest, total, and average salary of all employees.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.