Compare two columns in SQL

I’m new to SQL and have very basic queries in GCP.

Let’s consider this table below:

Name	B	C
Arun	1234-5678	1234
Tara	6789 – 7654	6789
Arun	4567	4324

Here, I want to compare column B and C and if they match then give 1 else 0 in column same and else different (which we have to create).

So here the catch:

• if column B has 1234-5678 and column C has 1234, then the column should match considering only the number before the “-” in the value.

The output should be :

Name	B	C	same	different
Arun	1234-5678	1234	1	0
Tara	6789 – 7654	6789	1	0
Arun	4567	4324	0	1

Also, I want to count the values of 1 for each values in Name for same and different columns.

So far I’ve tried this:

SELECT 
    name,
    b, 
    c , 
    if(b = c, 1, 0) as same, 
    if (b!=c,1,0) as different, 
    count(same), 
    count(different)
From Table

Answer

using “MySQL” (will work almost same with SQL server as well) here’s the possible solution.

Step 1) Setup table

CREATE TABLE Users (
    Name varchar(50),
    B varchar(50),
    C varchar(50)
);

INSERT INTO Users
VALUES
('Arun', '1234-5678', '1234'),
('Tara', '6789-7654', '6789'),
('Arun', '4567', '4324');

Step 2) same & different columns

SELECT
    Name, B, C,
    CASE WHEN SUBSTRING_INDEX(B, "-", 1) = C THEN 1 ELSE 0 END as same,
    CASE WHEN SUBSTRING_INDEX(B, "-", 1) <> C THEN 1 ELSE 0 END as different
FROM
    Users

Step 3) Join both results to get total_same & total_different for each user

SELECT
    Name,
    SUM(CASE WHEN SUBSTRING_INDEX(B, "-", 1) = C THEN 1 ELSE 0 END) as total_same,
    SUM(CASE WHEN SUBSTRING_INDEX(B, "-", 1) <> C THEN 1 ELSE 0 END) as total_different
FROM
    Users
GROUP BY Name

Reference: SQL Fiddle