-1

I'm working on GCP and I've this table below:

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

Here, I have compared B and C columns by taking the first 4 digits.
If B and C are same then '1' should come in another column I've created - Same
IF B and C are not same then, '1' should come in another column I've created - Different

Now, I want to find the count of same and different for each Name.
If count(same) > 1 then, '1' should come in another column 'YES', which we have to create
If count(different)> 1 then '1' should come in another columns 'NO' else 0 in all cases.

Basically, For Arun, Yes and No columns should come as 1.

So far I've tried this:

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,
    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,
    IF(SUM(CASE WHEN SUBSTRING_INDEX(B, "-", 1) = C THEN 1 ELSE 0 END) >1) 1, 0 as YES,
    IF(SUM(CASE WHEN SUBSTRING_INDEX(B, "-", 1) <> C THEN 1 ELSE 0 END) >1) 1, 0 as NO
FROM
    Users
GROUP BY Name,B,C

Expected output:

Name B C count(same) count(different) yes no
Arun 1234-5678 1234 1 1 1 1
Tara 6789 - 7654 6789 1 0 1 0

But it is not working. Can anyone please help?

Madness
  • 49
  • 7
  • 1
    Hey Madness, the answer from your last question didn't helped you ? What's the output that you need ? – LPK Jul 14 '22 at 13:41
  • Does this answer your question? [Compare two columns in SQL](https://stackoverflow.com/questions/72966674/compare-two-columns-in-sql) – LPK Jul 14 '22 at 13:49
  • @LPK, no It didn't work for count.. that's why posted another question regarding the counts. Please check the output table. – Madness Jul 14 '22 at 13:58
  • You can't have the expected output, because you have the name Arun twice with a different B and C column. It comes from the group by, so it will group by name, or group by name, b,c. If you don't put the columns B and C, you will have the good output for the yes and no. – LPK Jul 14 '22 at 14:46

1 Answers1

1

Try using a CTE to define the first table that you have, and then query directly from it and group by Name. Like so,

WITH subquery AS (
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
GROUP BY Name,B,C
)
SELECT Name, SUM(same) AS total_same, SUM(different) AS total_different
FROM subquery
GROUP BY Name

I used SUM() as a convenient way to "count 1's" because you explicitly say 1 or 0, so it works.

Josh
  • 1,493
  • 1
  • 13
  • 24
  • Hey Josh, it's basically the same output as https://stackoverflow.com/questions/72966674/compare-two-columns-in-sql/72967916#72967916 I don't know what @Madness needs... because it will be the same answer as his previous question. – LPK Jul 14 '22 at 13:51
  • 1
    @LPK no doubt... looks like EXACTLY the same question – Josh Jul 14 '22 at 13:57
  • Hey @Josh, thank you for this. If I'm joining 2 tables here so do I need to add prefix in 2nd SELECT statement for name? – Madness Jul 14 '22 at 13:59
  • @Madness not quite sure what you mean, but the 2nd SELECT statement is querying the results of the first. But I'm not sure what you mean by joining 2 tables. You're free to change the 1st to join in a way that gives you want you want, or modify the 2nd to join, its up to you and. your problem but I dont think your question has those details for us to say – Josh Jul 14 '22 at 14:03
  • I meant to say that if I'm joining two tables here, table1 as t1 and table2 as t2.. Then how will I query for the same problem? – Madness Jul 14 '22 at 14:04