0

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

4 Answers 4

1

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)
Sign up to request clarification or add additional context in comments.

2 Comments

sorry I made a mistake and I've been edit the question, yeah you code is showing a result, but what if there is zero on the table, and I don't want it to be counted, I'm trying is '0' or null but I got an error.
insert? Do you mean update another column with this count value
1
select
    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

1 Comment

I'm editing the question a little bit to be more easily understand, but I trying your code but showing error at from (values (CT.column1), (CT.column2), (CT.column3), (CT.column4)) as v(col)
0

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 

Comments

0

Try this query.

update set =sum(COUNT()-COUNT(column1) + COUNT()-COUNT(column2) + COUNT()-COUNT(column3) + COUNT()-COUNT(column4)) where =;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.