Compare two columns in SQL

Question

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 1

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

Answer 2

For the first step, you will need to SUBSTR the column b. We start at position 1 and we want 4 characters (only works if there's only 4 characters before the '-').

With table2 as (
  select name, b,c, same, different from (select name, b, c, case when (SUBSTR(b,1,4) = c)
  then '1' else '0' end as same, case when(SUBSTR(b,1,4)!= c) then '1' else '0' end as different
from Table1
group by name, b,c))

The WITH clause can be used when you have complex query, and if you want to create a temporary table in order to use it after.

The Table2 give you this :

After the WITH clause, you will have the second step, the count of same / different per name :

Select table1.name,count(table2.same+table2.different) as total from table1 
join table2 on (table2.name = table1.name and table2.b = table1.b)
group by table1.name;

The output give you the total per name (the name are group by, so in your example you will only have 2 rows, one for Arun with a total of 2 (same + different) and the other one with a total of 1)

So here's the entire code :

with table2 as (
  select name, b,c, same, different from (select name, b, c, case when (SUBSTR(b,1,4) = c) then '1' else '0' end as same, case when(SUBSTR(b,1,4)!= c) then '1' else '0' end as different
From Table1
group by name, b,c))

select table1.name, table1.b, table1.c, count(table2.same+table2.different) as total from table1 
join table2 on (table2.name = table1.name and table2.b = table1.b)
group by table1.name;