Geek Answers Handbook

How to represent functions of aggregate functions (NULL)?

I am looking for an example tutorial database to illustrate the basic functions of aggregate functions (Max, Min, Sum, Avg, and Count) when NULL values ​​are involved.

I should be able to discuss and illustrate / present the use of this aggregate function in the presence of NULL with sample queries and their answers using the specified database.

Many thanks!

+5
source share
2 answers

Using:

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

The conclusion should be:

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

, NULL , . COUNT - , * - COUNT(*), NULL.

+2

- . , count, .

korth.Here

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

(12131, jake, music, null), .

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

max avg. count .

0

More articles:


All Articles