CODEWITHSUNDEEP
mysql
user2851669
user2851669

Reputation:

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?

Upvotes: 1

Views: 1505

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

you can do the a diff this way 

 select (
      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)
  )  as my_diff
from dual;

Upvotes: 0

Related Questions