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)