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.
