user2851669 user2851669 - 1 month ago 10
MySQL Question

calculate difference between two rows in mysql

I have the following two tables -

Table1

TR1 TR2
1 10
2 15
3 20

Table2

TC1 TC2
10 100
15 150
20 200

select count(*) from table1, table2 where table1.tr2 = table2.tc1 and table1.tr1 > 1 and table1.tr2 in (select table2.tc1 from table2 where table2.tc1 > 10)

select count(*) from table1, table2 where table1.tr2 = table2.tc1 and table1.tr1 > 1 and table1.tr1 < 5 and table1.tr2 in (select table2.tc1 from table2 where table2.tc1 > 10)


2nd query will return a subset of the first query, I want to find the difference in count(*)s given by the two queries. How do I go about it?

Answer

You can always do

select (select count(*) ...) - (select count(*) ...);

But as your queries are so similar, you can merge the queries instead:

select
  count(*) - sum(t1.tr1 < 5)
from table1 t1
join table2 t2 on t1.tr2 = t2.tc1 
where t1.tr1 > 1
and t1.tr2 > 10;

I've changed your comma-separated join to a proper ANSI join. Your join syntax was made redundant in 1992. You shouldn't use it anymore.

I've replaced your IN clause with a simple t1.tr2 > 10, because you are already joining on t1.tr2 = t2.tc1.