CODEWITHSUNDEEP
sqloracle-database
CoderNewbie
CoderNewbie

Reputation: 21

Comparing column counts between 2 tables in Oracle

I have 2 tables with the same columns. One table has been updated and the other one is a back up. I need to compare the counts on a particular column that is present in the 2 tables and need to see what column counts are not the same.

I have this query thus far:

select a.id, count(a.last_name), b.id, count(b.last_name)
from updatedTable a
join backupTable b on a.id = b.id
group by a.contact_id, b.contact_id

The above query ran, but I want to know this: where count(a.last_name) <> count(b.last_name)

Upvotes: 1

Views: 1072

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

My guess is that you really want

SELECT *
  FROM (SELECT a.id, count(a.last_name) num_last_names
          FROM tableA a
         GROUP BY a.id) a
       INNER JOIN
       (SELECT b.id, count(b.last_name) num_last_names
          FROM tableB b
         GROUP BY b.id) b
       ON( a.id = b.id )
 WHERE a.num_last_names != b.num_last_names

This groups each table separately, then does the join and compares the data from the aggregated columns.

Upvotes: 1

Related Questions