w3resource

PostgreSQL Basic SELECT Statement: Calculate the monthly salary of each employee


19. Write a query to get a monthly salary (rounded up to 2 decimal places) of each employee.
Note : Assume that, the salary field provides the 'annual salary' information.

Sample Solution:

Code:

-- Selecting the first name, last name, and rounding the annual salary divided by 12 to two decimal places to represent the monthly salary
SELECT first_name, last_name, 
-- Calculating the monthly salary by dividing the annual salary by 12 and rounding the result to two decimal places, and labeling it as "Monthly Salary"
ROUND(salary/12,2) as "Monthly Salary" 
-- Selecting data from the employees table
FROM employees;

Explanation:

  • This SQL code selects the first name, last name, and calculates the monthly salary for each employee in the "employees" table.
  • The salary is divided by 12 to get the monthly equivalent, and the result is rounded to two decimal places using the ROUND function.
  • The result set will contain three columns: first name, last name, and the calculated monthly salary for each employee.

Sample table: employees


Output:

pg_exercises=# SELECT first_name, last_name,
pg_exercises-# ROUND(salary/12,2) as "Monthly Salary"
pg_exercises-# FROM employees;
 first_name  |  last_name  | Monthly Salary
-------------+-------------+----------------
 Steven      | King        |        2000.00
 Neena       | Kochhar     |        1416.67
 Lex         | De Haan     |        1416.67
 Alexander   | Hunold      |         750.00
 Bruce       | Ernst       |         500.00
 David       | Austin      |         400.00
 Valli       | Pataballa   |         400.00
 Diana       | Lorentz     |         350.00
 ....
 Den         | Raphaely    |         916.67
 Alexander   | Khoo        |         258.33
 Shelli      | Baida       |         241.67
 Sigal       | Tobias      |         233.33
 Guy         | Himuro      |         216.67
 Karen       | Colmenares  |         208.33
 Matthew     | Weiss       |         666.67
 Adam        | Fripp       |         683.33
 ....
 Steven      | Markle      |         183.33
 Laura       | Bissot      |         275.00
 Mozhe       | Atkinson    |         233.33
 James       | Marlow      |         208.33
 ....
 Stephen     | Stiles      |         266.67
 John        | Seo         |         225.00
 Joshua      | Patel       |         208.33
 Trenna      | Rajs        |         291.67
...
 Hermann     | Baer        |         835.83
 Shelley     | Higgins     |        1002.50
 William     | Gietz       |         694.17
(106 rows)

Practice Online



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

Previous: Write a query to select first ten records from a table.
Next: PostgreSQL Restricting and Sorting Data - Exercises, Practice, Solution

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.