CODEWITHSUNDEEP
mysql
domino
domino

Reputation: 7345

Mysql query check if number is x and count as 1

Mysql query question

How to count as 1 if is 1,2 or 5 for example

field1 = 1 field2= 1 field3= 5 field4= 2 field5=0; field6=6;

What I need is to check if any of these fields is 1, 2 or 5 and count them as 1 and ignore the rest.

SELECT field1+field2+field3+field4+field5+field6; FROM table WHERE id=1; // should return 4 (1+1+1+1+0+0)

Could this be done or am I going to have to do it with php?

Upvotes: 2

Views: 264

Answers (4)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can use a searched CASE statement but as been suggested by @eggyal, there might be a design flaw in your schema. Fixing that could very well simplify your requirement.

SELECT  CASE WHEN Field1 IN (1, 2, 5) THEN 1 ELSE 0 END
        + CASE WHEN Field2 IN (1, 2, 5) THEN 1 ELSE 0 END
        + CASE WHEN Field3 IN (1, 2, 5) THEN 1 ELSE 0 END
        + CASE WHEN Field4 IN (1, 2, 5) THEN 1 ELSE 0 END
        + CASE WHEN Field5 IN (1, 2, 5) THEN 1 ELSE 0 END
        + CASE WHEN Field6 IN (1, 2, 5) THEN 1 ELSE 0 END
FROM    Table

Upvotes: 1

user1474090
user1474090

Reputation: 675

Another option would be to use IF statements so something like this:

SELECT IF(field1 = 1 or field1 = 2 or field1 = 5, 1, 0)+IF(field2 = 1 or field2 = 2 or field2 = 5, 1, 0)+IF(field3 = 1 or field3 = 2 or field3 = 5, 1, 0)+IF(field4 = 1 or field4 = 2 or field4 = 5, 1, 0)+IF(field5 = 1 or field5 = 2 or field5 = 5, 1, 0)+IF(field6 = 1 or field6 = 2 or field6 = 5, 1, 0) FROM table

Upvotes: 1

Michael A
Michael A

Reputation: 9900

This should work:

select count(field1) + count(field2) + count(field3) + count(field4) + count(field5) + count(field6) as total
from table
where 
    field1 in(1,2,5)
    or field2 in(1,2,5)
    or field3 in(1,2,5)
    or field4 in(1,2,5)
    or field5 in(1,2,5)
    or field6 in(1,2,5)

edit: however eggyal makes a great point. Can you share more about your goal so a better solution could be found?

Upvotes: 1

eggyal
eggyal

Reputation: 125855

SELECT field1 IN (1,2,5) + field2 IN (1,2,5) + ...

However, this is somewhat indicative of a poor design; perhaps if you could provide further detail of the semantic meaning behind your data, we can suggest better alternatives?

Upvotes: 2

Related Questions