CODEWITHSUNDEEP
mysql
SilentCry
SilentCry

Reputation: 2092

MySQL - count rows if column value contains something

today I hit a stag when I realize that I need to count tags in database. I have table with tags column like

tags_column
tag1
tag1,tag2

tag1,somethingElse1
somethingElse1, somethingElse2
tag2
tag3,tag1

tag2

And I need to count how many rows contains e.g. tag1, tag2 and tag3 and how many rows ar without these tags. So I need something like

$qry = "SELECT COUNT(tags_Column) AS `tag1Count` WHERE `tags_Column` CONTAINS 'tag1', 
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `tag2Count` WHERE `tags_Column` CONTAINS 'tag2',
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `tag3Count` WHERE `tags_Column` CONTAINS 'tag3',
        (SELECT COUNT(tags_Column) FROM `TableContainsTags`) AS `notagCount` WHERE `tags_Column` DOESN'T_CONTAINS 'tag1' OR 'tag2' OR 'tag3'
        FROM `TableContainsTags`";

Do you have any idea, please?

Upvotes: 1

Views: 3132

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try:

SELECT sum( case when `tags_Column` CONTAINS 'tag2' then 1 else 0 end ) as tag2,
       sum( case when `tags_Column` CONTAINS 'tag3' then 1 else 0 end ) as tag3,
       .....
       sum( case when `tags_Column` CONTAINS 'tag256' then 1 else 0 end ) as tag256,
       sum( case when `tags_Column` CONTAINS `tag2` OR 
                      `tags_Column` CONTAINS 'tag3`
            then 0 else 1 end ) as doesnt_contains_tags_2_3
FROM `TableContainsTags`

Upvotes: 1

Related Questions