Count 'IN' value in MySQL from multiple non-normalized columns

Question

I am trying to count the amount of times an int or string value is presented in a table row from several columns in a non-normalized table.

SELECT count(*) from persons
WHERE '1' IN (`column 1`, `column 2`, `column 3`,`column 4`) AND person_MyId='14';

What I trying to solve is if it's possible to count the amount of times the value of 1 was located in the four columns without normalizing that table? I'm trying to see what I am missing in my query to pull that off.

you can refer to this link stackoverflow.com/questions/826365/… — Viswanath Polaki
– Viswanath Polaki, Commented Sep 17, 2024 at 8:33

Ergest Basha · Accepted Answer · 2024-09-17 07:54:23Z

2

Use SUM,

SELECT SUM( (col1 = 1) +  (col2 = 1) +  (col3 = 1) + (col4 = 1) ) AS totCount
FROM persons
WHERE person_MyId = 14;

See example

answered Sep 17, 2024 at 7:54

Ergest Basha

9,3164 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d-comet Over a year ago

this worked flawlessly, thanks Basha, three thumbs up my guy.

Ergest Basha Over a year ago

@d-comet I am glad it helped you. Please mark as accepted so future readers can benefit from this.

ArtBindu · Accepted Answer · 2024-09-17 07:53:26Z

-1

Use query:

SELECT count(*) 
from persons 
WHERE person_MyId='14'
AND (
     column_1 like '%1%'
  OR column_2 like '%1%'
  OR column_3 like '%1%'
  OR column_4 like '%1%'
);

answered Sep 17, 2024 at 7:53

ArtBindu

2,0381 gold badge12 silver badges21 bronze badges

Collectives™ on Stack Overflow

Count 'IN' value in MySQL from multiple non-normalized columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related