Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Sum if all rows are not null else return null in MySQL?
You can achieve this with the help of GROUP BY HAVING clause. The syntax is as follows −
SELECT yourColumnName1, SUM(yourCoumnName2) from yourTableName GROUP BY yourColumnName1 HAVING COUNT(yourCoumnName2) = COUNT(*);
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table SumDemo -> ( -> Id int, -> Amount int -> ); Query OK, 0 rows affected (0.58 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into SumDemo values(1,200); Query OK, 1 row affected (0.22 sec) mysql> insert into SumDemo values(2,100); Query OK, 1 row affected (0.19 sec) mysql> insert into SumDemo values(2,NULL); Query OK, 1 row affected (0.14 sec) mysql> insert into SumDemo values(1,300); Query OK, 1 row affected (0.16 sec) mysql> insert into SumDemo values(2,100); Query OK, 1 row affected (0.17 sec) mysql> insert into SumDemo values(1,500); Query OK, 1 row affected (0.16 sec)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from SumDemo;
Output
+------+--------+ | Id | Amount | +------+--------+ | 1 | 200 | | 2 | 100 | | 2 | NULL | | 1 | 300 | | 2 | 100 | | 1 | 500 | +------+--------+ 6 rows in set (0.00 sec)
Here is the query to get the sum if all rows are not null else return null. The query is as follows −
mysql> select Id, -> SUM(Amount) -> from SumDemo -> GROUP BY ID -> HAVING COUNT(Amount) = COUNT(*);
The following is the output. Since id 2 is NULL, none of its values will be added to the sum.
Therefore, all the values of Id 1 would be added i.e. 200 + 300 + 500 = 1000 as shown below −
+------+-------------+ | Id | SUM(Amount) | +------+-------------+ | 1 | 1000 | +------+-------------+ 1 row in set (0.09 sec)
Updated on: 30-Jul-2019
393 Views
Advertisements