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
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.
© 2024 OneMinuteCode. All rights reserved.