How to count horizontal values on a MySQL database?

You can use aggregate function COUNT() from MySQL to count horizontal values on a database. Let us first create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstValue int,
   SecondValue int,
   ThirdValue int,
   FourthValue int
);
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">FirstValue</span><span class="pun">,</span><span class="typ">SecondValue</span><span class="pun">,</span><span class="typ">ThirdValue</span><span class="pun">,</span><span class="typ">FourthValue</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(-</span><span class="lit">18</span><span class="pun">,</span><span class="lit">45</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">155</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.22</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">FirstValue</span><span class="pun">,</span><span class="typ">SecondValue</span><span class="pun">,</span><span class="typ">ThirdValue</span><span class="pun">,</span><span class="typ">FourthValue</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">0</span><span class="pun">,</span><span class="lit">235</span><span class="pun">,</span><span class="kwd">null</span><span class="pun">,</span><span class="lit">15</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.20</span><span class="pln"> sec</span><span class="pun">)</span>

Following is the query to display all records from the table using select statement −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

This will produce the following output −

+----+------------+-------------+------------+-------------+
| Id | FirstValue | SecondValue | ThirdValue | FourthValue |
+----+------------+-------------+------------+-------------+
| 1  | -18        | 45          | 0          | 155         |
| 2  | 0          | 235         | NULL       | 15          |
+----+------------+-------------+------------+-------------+
2 rows in set (0.00 sec)

Here is the query to count horizontal values on a database −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SELECT</span>
<span class="pln">   COUNT</span><span class="pun">(</span><span class="typ">FirstValue</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> OR NULL</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span>
<span class="pln">   COUNT</span><span class="pun">(</span><span class="typ">SecondValue</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> OR NULL</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span>
<span class="pln">   COUNT</span><span class="pun">(</span><span class="typ">ThirdValue</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> OR NULL</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span>
<span class="pln">   COUNT</span><span class="pun">(</span><span class="typ">FourthValue</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> OR NULL</span><span class="pun">)</span><span class="pln"> AS TOTAL_VALUE</span>
<span class="pln">FROM </span><span class="typ">DemoTable</span><span class="pun">;</span>

This will produce the following output −

+-------------+
| TOTAL_VALUE |
+-------------+
| 4           |
+-------------+
1 row in set (0.00 sec)