NULL entries are included when using the SQL aggregation function.

Asked 2 years ago, Updated 2 years ago, 27 views

INSERT INTO customers (customer_id, customer_name, age, ranking, job, savings)
VALUES ('apple', 'Hong Gil-dong', 20, 'gold', 'student', 1000),
       ('banana', 'Kim Cheol-soo', 25, 'vip', 'Nurse', 2500),
       ('carrot', 'Park Young-hee', 28, 'gold', 'teacher', 4500),
       ('orange', 'Choi Yongwook', 22, 'silver', 'student', 0),
       ('melon', 'Sung Won-yong', 35, 'gold', 'office worker', 5000),
       ('peach', 'Oh Hyeongjun', NULL, 'silver', 'Doctor', 300),
       ('pear', 'glowers', 31, 'silver', 'office worker' 500);

You have created these tables in a database. As you can see, the value of the age column of the tuple with the value of the customer_id column 'peach' is NULL.

Here, to get the total of the age values for all tuples, we entered the following SQL statement:

SELECT COALESCE(SUM(age), 0) FROM customers;
> 185

It should be 20+25+28+22+35+31=161, but an unknown value of 24 was added and 185 was output.

If there is a NULL value, we also added the COALESCE() function to replace it with 0. I wonder how I can get a normal sum...

The DBMS you are using is MariaDB.

sql

2022-09-22 16:43

1 Answers

https://www.techonthenet.com/sql_server/is_not_null.php

If you add IS NOT NULL to the where statement, the NULL value will not be taken in the first place.


2022-09-22 16:43

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.