How to present features of aggregate functions (NULL)?

Use:

SELECT MAX(t.num) AS max_test,        MIN(t.num) AS min_test,       SUM(t.num) AS sum_test,       AVG(t.num) AS avg_test,       COUNT(t.num) AS count_test,       COUNT(*) AS count_star_test  FROM (SELECT NULL AS num        UNION ALL        SELECT 1        UNION ALL        SELECT 2        UNION ALL        SELECT 3) t

Output should be:

max_test | min_test | sum_test | avg_test | count_test | count_star_test-------------------------------------------------------------------------3        | 1        | 6        | 2        | 3          | 4

In summary, NULL is ignored by aggregate functions if you reference the column specifically. COUNT is the only aggregate function that supports * - COUNT(*) will include NULLs in this case.

Null values are the value which is unknown.All aggregate functions except the count function ignores the null value.

I have taken example form database system concepts by korth.Here is an instructor relation

ID        name        dept        salary22222     Einstein    Physics     95000 12121     Wu          Finance     9000032343     El Said     History     60000 45565     Katz        Comp. Sci.  75000 98345     Kim Elec.   Eng.        80000 12131     jake        music       null

I have ignored few tuples and inserted a new tuple (12131,jake,music,null) to illustrate aggregate function on null values.

select sum(salary) from instructor;result 400000select min(salary) from instructor;result 60000select count(*) from instructor;result 6

Similar result holds for max and avg function.Only the count function takes in account of null values.