siddharth raghunath siddharth raghunath - 1 year ago 79
MySQL Question

Count specific value across different columns in a row

I have a table that has the below columns:

- request_id (Auto increment)
- is_requested_data1 - (Enum (y,n)
- is_requested_data2 - (Enum (y,n)
- is_requested_data3 - (Enum (y,n)
- is_requested_data4 - (Enum (y,n)

For a specific row , lets say request_id = 1 , i want to know the count of 'y'.
I have tried declaring a variable and tried to increment the value of the variable everytime there is a 'y' for any column using mysql cases but it doesn't seem to work .

Please advice .

Answer Source

You don't need COUNT but +.

        (is_requested_data1 = 'y') + 
        (is_requested_data2 = 'y') + 
        (is_requested_data3 = 'y') + 
        (is_requested_data4 = 'y')
    ) AS totalYs
WHERE request_id = 1

Note: Since MySQL boolean expression resolves into 0/1. So that whenever a column matches to y it returns 1 otherwise 0.

So, if is_requested_data1 is equal to y then the following returns 1

is_requested_data1 = 'y' returns 1

WORKING DEMO For convenience I've used the column names A, B, C, D.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download