Row count comparison for 2 tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Row count comparison for 2 tables

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Row count comparison for 2 tables

    Need to compare the rowcount for 2 tables to see if they're identical or not, in a single sql statement. Here's what I got...

    Code:
    SELECT TABLE_NAME,(CASE 
                        WHEN (SELECT COUNT(*) FROM TMP_JP_W_PERSON) = 
                             (SELECT COUNT(*) FROM W_PERSON) 
                        THEN 'ROWMATCH' 
                        ELSE 'NO MATCH'
                       END) "NUM OF ROWS" 
    FROM USER_TABLES 
    WHERE TABLE_NAME = 'TMP_JP_W_PERSON';
    
    TABLE_NAME                NUM OF ROWS
    ------------------------- -----------
    TMP_JP_W_PERSON           NO MATCH

    There has to be a much more elegant way of doing this. Code is not my strong point.

    Any help would be appreciated.

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    What about:

    Code:
    select a.count, b.count, a.count-b.count diff
    from 	(
    	select count(*) count from table A
    	) a
    	(
    	select count(*) count from table B
    	) b;

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Yeah, that's given me an idea, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width