PostgreSQL Aggregate Functions and Group By: Get the number of employees working in each post

7. Write a query to get the number of employees working in each post.

Sample Solution:

Code:

-- This SQL query retrieves the count of employees for each job title.

SELECT job_id, COUNT(*) -- Selects the job_id column and counts the number of occurrences for each job_id
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
GROUP BY job_id; -- Groups the results by the job_id column, so that the count is calculated for each unique job_id

Explanation:

• The SQL query retrieves information about the number of employees for each job title.
• The SELECT statement is used to specify the columns to be retrieved from the table.
• job_id is selected to identify each job title, and COUNT(*) is used to count the number of occurrences for each job_id.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table in this case.
• The GROUP BY clause is used to group the results by the job_id column, ensuring that the count is calculated for each unique job_id.

Sample table: employees

Output:

pg_exercises=# SELECT job_id, COUNT(*)
pg_exercises-# FROM employees
pg_exercises-# GROUP BY job_id;
   job_id   | count
------------+-------
 Salesman   |     5
 AC_ACCOUNT |     1
 IT_PROG    |     5
 SA_MAN     |     5
 AD_PRES    |     1
 AC_MGR     |     1
 FI_MGR     |     1
 AD_ASST    |     1
 MK_MAN     |     1
 PU_CLERK   |     5
 HR_REP     |     1
 PR_REP     |     1
 FI_ACCOUNT |     5
 SH_CLERK   |    20
 AD_VP      |     2
 SA_REP     |    29
 ST_CLERK   |    20
 MK_REP     |     1
 PU_MAN     |     1
(19 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to get the highest, lowest, total, and average salary of all employees.
Next: Write a query to get the difference between the highest and lowest salaries.