Usage of avg function in sql

The AVG function in SQL is used to calculate the average of numeric values. Its syntax is AVG(column_name). AVG functions ignore NULL values. You can use the AS keyword to specify aliases for results. It only works for numeric type data, returning NULL if the target column has no value or contains only NULL values, and throwing an error if it contains non-numeric values.

Usage of AVG function in SQL

The AVG function in SQL is used to calculate the value of a set of numeric values average value.

Syntax:

<code>AVG(column_name)</code>

Where:

• column_name is the target column to calculate the average.

Usage:

<code>-- 示例表包含 'sales' 列，存储销售数据
CREATE TABLE sales (
    product_id INT,
    sales INT
);

-- 计算 'sales' 列的平均值
SELECT AVG(sales) FROM sales;</code>

Return result:

<code>NULL</code>

Because there is no data in the table yet, the result is NULL.

Handling NULL values:

AVG functions ignore NULL values. For example:

<code>-- 在 'sales' 列中插入一些数据，包括 NULL 值
INSERT INTO sales (product_id, sales) VALUES
(1, 10),
(2, 20),
(3, NULL);

-- 计算 'sales' 列的平均值
SELECT AVG(sales) FROM sales;</code>

Returns:

<code>15.0</code>

The AVG function ignores NULL values, so the average is (10 20) / 2 = 15.

Using aliases:

You can use the AS keyword to specify an alias for the result of an AVG function. For example:

<code>-- 计算 'sales' 列的平均值并将其别名为 'average_sales'
SELECT AVG(sales) AS average_sales FROM sales;</code>

Return result:

<code>| average_sales |
|---|---|
| 15.0 |</code>

Note:

• AVG function only applies to numeric types The data.
• If the target column has no values ​​or contains only NULL values, the AVG function returns NULL.
• The AVG function throws an error if the target column contains non-numeric values.

The above is the detailed content of Usage of avg function in sql. For more information, please follow other related articles on the PHP Chinese website!