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)
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,
Thanks for the information Reese.
Don't forget to share your valuable comments!
All SQL math functions list : postgresql.org/docs/9.5/functions-...