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
