Count number of text coincidence in all columns of a row

Question

I am trying to count the number of times that a word appears in all the columns of a row, done an id.

+-------+------+------+------+------+------+------+
| id    | pos1 | pos2 | pos3 | pos4 | pos5 | pos6 |
+-------+------+------+------+------+------+------+
| core1 | AA   | BB   | CC   | HH   | YY   | var1 |
| core2 | AA   | BB   | var3 | TT   | var2 | YY   |
| core5 | AA   | BB   | EE   | GG   | YY   | ZZ   |
+-------+------+------+------+------+------+------+

I've tried with this code but only returns '1' and not '2' that is what I want to.

SELECT count(id) FROM customize WHERE pos1 OR pos2 OR pos3 OR pos4 OR pos5 OR pos6 LIKE 'var%' AND id = 'core2';

Any ideas of how could I do it?

Presumably you mean 'all the rows of a column'? Either way, normalize your design. — Strawberry, Mar 12 '14 at 15:11
possible duplicate of http://stackoverflow.com/questions/5649711/mysql-count-amount-of-times-the-words-occur-in-a-column — BRBT, Mar 12 '14 at 15:13

score 1 · Accepted Answer · answered Mar 12 '14 at 15:21

1

SELECT 
    (IF(pos1 LIKE 'var%', 1, 0) +
    IF(pos2 LIKE 'var%', 1, 0) +
    IF(pos3 LIKE 'var%', 1, 0) +
    IF(pos4 LIKE 'var%', 1, 0) +
    IF(pos5 LIKE 'var%', 1, 0) +
    IF(pos6 LIKE 'var%', 1, 0)) AS done
FROM customize
WHERE id = 'core2'

answered Mar 12 '14 at 15:21

smozgur

1,772
1
15
23

If you want to search for all ids instead specifying id then you can add id into the SELECT and GROUP BY id at the end of the SQL. – smozgur Mar 12 '14 at 15:24
actually you don't need the group by, just remove the where clause and add id to the select list – Kevin Mar 12 '14 at 15:36
1

What if more than one id, @Kevin :) There is no indication about pk, so I wanted to make sure it works if id is not pk as well. – smozgur Mar 12 '14 at 15:46

score -1 · Answer 2 · answered Mar 12 '14 at 15:14

Maybe do a load of unioned queries (one for each column) and then count the results.

SELECT COUNT(*) 
FROM
(
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos1 LIKE 'var%'
    UNION ALL
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos2 LIKE 'var%'
    UNION ALL
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos3 LIKE 'var%'
    UNION ALL
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos4 LIKE 'var%'
    UNION ALL
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos5 LIKE 'var%'
    UNION ALL
    SELECT id
    FROM customize 
    WHERE id = 'core2'
    AND pos6 LIKE 'var%'
)

Or if you want a count of the rows that have a column with that value:-

SELECT COUNT(id)
FROM customize 
WHERE id = 'core2'
AND (pos1 LIKE 'var%' OR pos2 LIKE 'var%' OR pos3 LIKE 'var%' OR pos4 LIKE 'var%' OR pos5 LIKE 'var%' OR pos6 LIKE 'var%')

But better to normalise your database design

score -1 · Answer 3 · answered Mar 12 '14 at 15:20

-1

I think you might want something more like this:

    select 
     IF(Pos1 LIKE 'var%', 1, 0) +
     IF(Pos2 LIKE 'var%', 1, 0) +
     IF(Pos3 LIKE 'var%', 1, 0) +
     IF(Pos4 LIKE 'var%', 1, 0) +
     IF(Pos5 LIKE 'var%', 1, 0) +
     IF(Pos6 LIKE 'var%', 1, 0) 
 FROM customize
    where id = 'core2'

answered Mar 12 '14 at 15:20

Kevin

7,162
11
46
70

If you want count for each id, just remove the where clause and add id to the select list – Kevin Mar 12 '14 at 15:35

score -1 · Answer 4 · answered Mar 12 '14 at 15:30

It always helps to try to break down a problem conceptually. There are two stages to solving this problem. The first is to pivot your input table, converting the columns into rows.

The second is to count the values of the strings.

Here is a query to do both (http://sqlfiddle.com/#!2/3731e/7/0).

SELECT COUNT(*) AS stringcount, pos AS string
FROM (  -- pivot the original table
                  SELECT id, 1 AS i, pos1 AS Pos FROM customize
        UNION ALL SELECT id, 2 AS i, pos2 AS Pos FROM customize
        UNION ALL SELECT id, 3 AS i, pos3 AS Pos FROM customize
        UNION ALL SELECT id, 4 AS i, pos4 AS Pos FROM customize
        UNION ALL SELECT id, 5 AS i, pos5 AS Pos FROM customize
        UNION ALL SELECT id, 6 AS i, pos6 AS Pos FROM customize
) AS pivoted
GROUP BY pos
ORDER BY COUNT(*) DESC, pos

Count number of text coincidence in all columns of a row

4 Answers4