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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With