How to count a table column that not Null or Zero and Insert the result into "Count" column like table down below in Mysql?
column1 | column2 | column3 | column4 | Count
3 2 4 2 4
2 2 3 0 3
3 0 5 2 3
0 0 2 3 2
0 0 6 2 2
One way is simply using case statement:
Edit:
For update cnt column in your table_name, you can use:
update table_name
set cnt = (case when column1 is null or column1 = 0 then 0 else 1 end +
case when column2 is null or column2 = 0 then 0 else 1 end +
case when column3 is null or column3 = 0 then 0 else 1 end +
case when column4 is null or column4 = 0 then 0 else 1 end)
update another column with this count valueselect
CT.Column1,
CT.Column2,
CT.Column3,
CT.Column4,
(
select count(*)
from (values (CT.column1), (CT.column2), (CT.column3), (CT.column4)) as v(col)
where v.col is not null
) as Count
from customtable as CT
from (values (CT.column1), (CT.column2), (CT.column3), (CT.column4)) as v(col)first in your table on your 3rd line the expected count is 3 right ? A dirty way of doing this would be to sum the integer values of the test is the column NULL (boolean as int is 1 or 0 ):
SELECT
Column1, Column2, Column3, Column4,
(CAST(column1 is not null AS SIGNED INTEGER) +
CAST(column2 is not null AS SIGNED INTEGER) +
CAST(column3 is not null AS SIGNED INTEGER) +
CAST(column4 is not null AS SIGNED INTEGER)) as count
FROM (
SELECT 3 as column1,2 as column2,4 as column3,2 as column4
union select 2,2,3,NULL
union select 3,NULL,5,2
union select NULL,NULL,2,3
union select NULL,NULL,6,2
) as YourTable
Try this query.
update set =sum(COUNT()-COUNT(column1) + COUNT()-COUNT(column2) + COUNT()-COUNT(column3) + COUNT()-COUNT(column4)) where =;
