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 : [e-mail]

Comments

[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,

[Xaman]

Thanks for the information Reese.

[Xaman]

Don't forget to share your valuable comments!

[Xaman]

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