FIND_IN_SET sql query

Question

I have query like below.

SELECT Id, FIND_IN_SET('22', Category ) >0 AS `tot_cat` 
FROM tbl_doctorprofile

I am getting result 1 in which row i am getting value '22' else 0.. so result is like below.

Id tot_cat
----------
1    0
2    0
3    1
4    0

But i want only count of rows which has value 1.

Answer 1

So just sum the column:

SELECT SUM(FIND_IN_SET('22', Category ) >0) AS `tot_cat` 
FROM tbl_doctorprofile

or formally a bit better:

SELECT COUNT(*)
WHERE FIND_IN_SET('22', Category ) > 0
FROM tbl_doctorprofile

Answer 2

Try this:

SELECT Id, 
       COUNT(CASE WHEN FIND_IN_SET('22', Category ) > 0 THEN 1 END) AS `tot_cat` 
FROM tbl_doctorprofile

Answer 3

You can use below query it must help you

 SELECT COUNT(1) FROM 
(SELECT Id, FIND_IN_SET('22', Category ) >0 AS `tot_cat` 
FROM tbl_doctorprofile ) t WHERE t.tot_cat!=0;

Answer 4

Did you want this?

SELECT Id, FIND_IN_SET('22', Category ) >0 AS `tot_cat` 
FROM tbl_doctorprofile
HAVING `tot_cat` = 1