CODEWITHSUNDEEP
sqldatabaseaggregate-functions
Mick Orwell
Mick Orwell

Reputation: 51

How to present features of aggregate functions (NULL)?

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!

Upvotes: 5

Views: 3414

Answers (2)

user3820950
user3820950

Reputation:

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        salary
22222     Einstein    Physics     95000 
12121     Wu          Finance     90000
32343     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 400000
select min(salary) from instructor;
result 60000
select count(*) from instructor;
result 6

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 2

Related Questions