CODEWITHSUNDEEP
sqlgoogle-cloud-platform
Madness
Madness

Reputation: 39

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:

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

Upvotes: 0

Views: 208

Answers (2)

TalESid
TalESid

Reputation: 2514

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');

Users List

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

smae & different query result

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

total same & different for each user

Reference: SQL Fiddle

Upvotes: 2

LPK
LPK

Reputation: 536

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 :

Output Table2

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)

Final Output

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;

Upvotes: 1

Related Questions