What are SQL aggregate functions and how do they handle NULL values?

What are SQL aggregate functions and how do they handle NULL values?

SQL aggregate functions perform calculations on a set of values and return a single value. Common examples include COUNT, SUM, AVG, MIN, and MAX. When it comes to NULL values, most aggregate functions ignore them. For instance, if you use SUM on a column with NULLs, those NULLs won't be included in the total.

Can you give me an example of how SUM works with NULL values?

Sure! If you have a table with a column 'sales' that contains the values 100, NULL, 200, and 300, using SUM(sales) will return 600, as the NULL value is ignored.

What about COUNT? Does it count NULL values?

The COUNT function behaves differently. COUNT(column_name) will count all non-NULL values, while COUNT() counts all rows, including those with NULLs. So in the previous example, COUNT(sales) would return 3, but COUNT() would return 4.

Are there any aggregate functions that include NULL values?

No standard aggregate functions include NULL values in their calculations. However, you can use COALESCE to replace NULLs with a default value before applying an aggregate function.