Batista F Batista F - 2 months ago 16
MySQL Question

Count values using a criteria

I have the following db table, and I would like to be able to complete the column "Results" comparing with column C1.

Example:

I have 2 letters "C" on column C2, but in column C1 I have 2 different numbers (3 and 4). I need to complete the "Result" with 1.

I have 2 letters "A" on column C2, but in column C1 I have the same number (1). I need to complete the "Result" with 0.

enter image description here

Thanks.

Answer

Use COUNT(DISTINCT) to see if the C1 values are the same or different, and then IF() to convert that to 0 or 1.

SELECT C2, IF(COUNT(DISTINCT C1) = 1, 0, 1) AS Result
FROM yourTable
GROUP BY C2

To get this into the Result column of the original table, you can join this subquery with the table.

SELECT t1.C1, t1.C2, t2.Result
FROM yourTable
JOIN (the above subquery) AS t2 ON t1.C2 = t2.C2
Comments