SQL Server LOG10() Function

In SQL Server, the LOG10() function is used to return the logarithm to the base 10 of a given number. In mathematics, the logarithm to the base 10 of a number refers to the power to which 10 must be raised to give the number. For example, the logarithm to the base 10 of 10 is 1, the logarithm to the base 10 of 100 is 2, and the logarithm to the base 10 of 1 is 0.

Syntax

The syntax of the LOG10() function is as follows:

LOG10 ( float_expression )

Here, float_expression refers to a floating-point expression, which can be a constant, a variable, or a function that returns a floating-point number.

Use cases

The logarithm of a number is often required in various mathematical and statistical analyses. The LOG10() function is commonly used to calculate the logarithm to the base 10 and is useful in the following scenarios:

  • Calculating exponential growth or decay of data
  • Comparing the size of data
  • Calculating the rate of change of data
  • Calculating percentages based on data

Examples

Here are two examples of using the LOG10() function:

Example 1

Suppose we have a table sales that contains the sales quantity and revenue of products. We want to calculate the logarithm to the base 10 of the average unit price of each product.

SELECT AVG(sales.amount / sales.quantity) AS AvgPrice, LOG10(AVG(sales.amount / sales.quantity)) AS Log10AvgPrice
FROM sales
GROUP BY sales.product_id;

The above query will return the average unit price and the logarithm to the base 10 of the average unit price for each product.

Example 2

Suppose we have a table temperature that contains the daily maximum temperature. We want to calculate the logarithm to the base 10 of the average temperature of each month.

SELECT YEAR(temperature.date) AS Year,
    MONTH(temperature.date) AS Month,
    AVG(temperature.high) AS AvgHigh,
    LOG10(AVG(temperature.high)) AS Log10AvgHigh
FROM temperature
GROUP BY YEAR(temperature.date), MONTH(temperature.date)
ORDER BY Year, Month;

The above query will return the average temperature and the logarithm to the base 10 of the average temperature for each month.

Conclusion

The LOG10() function is a SQL Server function used to return the logarithm to the base 10 of a number. It is commonly used to calculate the exponential growth or decay of data, compare the size of data, calculate the rate of change of data, or calculate percentages based on data.