CODEWITHSUNDEEP
mysql
Jericon
Jericon

Reputation: 5172

Comparing 2 Columns in same table

I need to compare 2 columns in a table and give 3 things:

I've been able to get just rows matching using a join on itself, but I'm unsure how to get the others all at once. The importance of getting all of the information at the same time is because this is a very active table and the data changes with great frequency.

I cannot post the table schema as there is a lot of data in it that is irrelevant to this issue. The columns in question are both int(11) unsigned NOT NULL DEFAULT '0'. For purposes of this, I'll call them mask and mask_alt.

Upvotes: 5

Views: 14610

Answers (2)

Bohemian
Bohemian

Reputation: 424983

select
    count(*) as rows_checked,
    sum(col = col2) as rows_matching,
    sum(col != col2) as rows_different
from table

Note the elegant use of sum(condition).
This works because in mysql true is 1 and false is 0. Summing these counts the number of times the condition is true. It's much more elegant than case when condition then 1 else 0 end, which is the SQL equivalent of coding if (condition) return true else return false; instead of simply return condition;.

Upvotes: 8

Michael Berkowski
Michael Berkowski

Reputation: 270609

Assuming you mean you want to count the rows where col1 is or is not equal to col2, you can use an aggregate SUM() coupled with CASE:

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN col = col2 THEN 1 ELSE 0 END )AS matching,
  SUM(CASE WHEN col <> col2 THEN 1 ELSE 0 END) AS non_matching
FROM table

It may be more efficient to get the total COUNT(*) in a subquery though, and use that value to subtract the matching to get the non-matching, if the above is not performant enough.

SELECT
  total,
  matching,
  total - matching AS non_matching
FROM
(
  SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN col = col2 THEN 1 ELSE 0 END )AS matching
  FROM table
) sumtbl

Upvotes: 2

Related Questions