Count the number of occurrences of a string across multiple attributes in MySQL Database How

Question

How can I count the number of occurrences of a string across multiple attributes (in my current case, I have 5 attributes) of a table in a MySQL database? Can I do so using MySQL quer(y/ies)?

The attributes may have NULL values, if that means anything while counting.

Also, I am using PHP on the client-side which I could use if MySQL wouldn't be the better way to find this count, though I want to know how this can be done with MySQL.

I saw this post, and it's similar to what I want to do, but this solution only works for counting strings over one attribute/column. I need to count the total occurrences of each string over all (e.g.: 5) of my attributes/columns.

Sounds like you need to re-design your database. Maybe have a table dedicated to id, attribute. — Devon Bessemer
– Devon Bessemer, Commented Apr 19, 2015 at 0:08
Table_name(id, attr_1, attr_2, attr_3, attr_4, attr_5) is what I have right now. — user3773048
– user3773048, Commented Apr 19, 2015 at 0:11
It seems by your question, a value could exist in any of these, so why do you have attributes separated into five columns? — Devon Bessemer
– Devon Bessemer, Commented Apr 19, 2015 at 0:15
They're different fields that a user could add the string to from an HTML form. There are a total of five possible fields. The user must put a string (e.g.: with a drop-down) in at least one of the fields (attr_1). The other four attr fields may be NULL (or contain empty strings, haven't figured those details out yet). — user3773048
– user3773048, Commented Apr 19, 2015 at 0:21
That is just an unusual database structure. The columns should be something more specific in my opinion. Otherwise, it would make sense just to use one column for attribute, and multiple rows which would make this query much more simple. — Devon Bessemer
– Devon Bessemer, Commented Apr 19, 2015 at 0:26

score 0 · Accepted Answer · 2015-04-19 00:36:55Z

0

declare @string nvarchar(255) = "string" --String to search for

select COUNT(t1.attr_1) + COUNT(t2.attr_2) + COUNT(t3.attr_3) + COUNT(t4.attr_4) + COUNT(t5.attr_5)
from Table_name t
join Table_name t1 on t1.id = t.id
join Table_name t2 on t2.id = t.id
join Table_name t3 on t3.id = t.id
join Table_name t4 on t4.id = t.id
join Table_name t5 on t5.id = t.id
where t1.attr_1 = @string
and t2.attr_2 = @string
and t2.attr_3 = @string
and t2.attr_4 = @string
and t2.attr_5 = @string

edited Apr 19, 2015 at 0:36

answered Apr 19, 2015 at 0:25

user4570983

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

2 Comments

user4570983 Over a year ago

This query counts the total number of occurrences of @string across the 5 columns. Maybe I'm not understanding your question correctly. Could you provide an example of what you're trying to do?

user3773048 Over a year ago

Yeah, you didn't understand my question properly, but Devon got me to my solution in the comment-thread above. Thanks anyways!

Collectives™ on Stack Overflow

Count the number of occurrences of a string across multiple attributes in MySQL Database How

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related