Comparing 2 Columns in same table

Question

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

• Count of rows checked (Total Rows that were checked)
• Count of rows matching (Rows in which the 2 columns matched)
• Count of rows different (Rows in which the 2 columns differed)

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.

Answer 1

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;.

Answer 2

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