i have two sql tables i want to compare two tables ..comparing two tables is done now what i want is how many records are matched and not matched in the form of percentage .is it possible to write a query in sql to get percentage? and here is my tables

 src_table
----------------------------------------------------------------
src_table
----------------------------------------------------------------
1
2
3
a
b
c
a@
b@
c@
-----------------------------------------------------------------

and tgt_table is

-----------------------------------------------------------------
tgt_data
-----------------------------------------------------------------
1
4
5
a
e
f
a@
e@
f@
--------------------------------------------------------------------

and now i want matched data and unmatched in the form of percentage is it possible?

1 Answer

I assume you wish to calculate the percentage over the table tgt_data. If this is not the case, just exchange the table names below.

The query depends on the capabilities of your SQL product. If it cannot handle complex expressions with sub-queries, you will need to break the expression into multiple statements.

The expression you would like to calculate is:

(select count(*) * 100 from tgt_data t where exists (select * from src_table where src_col = t.tgt_col))
/
(select count(*) from tgt_data)

If you need to formulate it as one SELECT statement, this is an alternate formulation:

select
(select count(*) * 100 from tgt_data t where exists (select * from src_table where src_col = t.tgt_col))
/ count(*)
from tgt_data

If your product calculates this in integer arithmetic, and you would like the result in decimal, then replace 100 by 100.0 above.