How to present features of aggregate functions (NULL)?

Question

I'm looking for 'textbook' example of database to illustrate salient features of the aggregate functions (Max, Min, Sum, Avg and Count) when NULL values are involved.

I must be able to discuss and illustrate/present the usage of these aggregates function in the presence of NULLs with example queries and their answers, using mentioned database.

Many thanks!

Answer 1

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.