Batista F - 8 months ago 56

MySQL Question

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.

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
```