DEV Community

Cover image for SQL Math Functions with Use Cases
Ashfiquzzaman Sajal
Ashfiquzzaman Sajal

Posted on

SQL Math Functions with Use Cases

In this context,

  • ABS(x): Returns the absolute value of the input value 'x'. For example, ABS(-10) would return 10.

  • ROUND(x, d): Rounds the input value 'x' to the nearest whole number or to the specified number of decimal places 'd'. For instance, ROUND(3.14159, 2) would return 3.14.

  • CEILING(x): Returns the smallest integer value greater than or equal to the input value 'x'. For example, CEILING(4.25) would return 5.

  • FLOOR(x): Returns the largest integer value less than or equal to the input value 'x'. For instance, FLOOR(4.75) would return 4.

  • POWER(x, y): Raises the input value 'x' to the power 'y'. For example, POWER(2, 3) would return 8.

  • SQRT(x): Returns the square root of the input value 'x'. For instance, SQRT(16) would return 4.

Here are five advanced SQL queries that utilize SQL math functions:

Calculate the average salary of employees, rounding the result to two decimal places.

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

Find the square root of the total sales for each product category.

SELECT category, SQRT(SUM(sales)) AS square_root_sales
FROM products
GROUP BY category;

Calculate the total revenue, rounding it to the nearest thousand.

SELECT ROUND(SUM(price * quantity), -3) AS total_revenue
FROM orders;

Find the ceiling value of the average rating for each product.

SELECT product_id, CEILING(AVG(rating)) AS ceiling_rating
FROM reviews
GROUP BY product_id;

Calculate the power of the discount percentage for each product.

SELECT product_id, POWER(discount, 2) AS discount_power
FROM products;

Hopefully you find this article helpful. Share your suggestion in comment.

Follow me in Linkedin, Instagram, Twitter, Github.
Email : ashsajal@yahoo.com

Top comments (4)

Collapse
 
aarone4 profile image
Aaron Reese

If you need to include more information than just the grouped filed and aggregate you can also use the OVER() syntax.

SELECT category, SQRT(SUM(sales)) AS square_root_sales
FROM products
GROUP BY category;

becomes
SELECT category, ProductName, SQRT(SUM(sales) OVER (PARTITION BY category)) AS square_root_sales_of_category
FROM products;

This will aggregate the sales by category and display the result for the product category against the product row,

Collapse
 
ashsajal profile image
Ashfiquzzaman Sajal

Thanks for the information Reese.

Collapse
 
ashsajal profile image
Ashfiquzzaman Sajal

Don't forget to share your valuable comments!

Collapse
 
ashsajal profile image
Ashfiquzzaman Sajal

All SQL math functions list : postgresql.org/docs/9.5/functions-...