I have two table with same name in two different schemas (old and new dump). I would like to know the difference between the two integration.

I have two queries, that gives old and new count:

select count(*) as count_old from(
        SELECT 
            distinct id
        FROM 
            schema1.compound)q1
    

select count(*) as count_new from(     
        SELECT 
            distinct id
        FROM 
            schema2.compound)q2

I would like have the following output.

table_name  count_new count_new diff
compound    4740      4735      5

Any help is appreciated. Thanks in advance

CodePudding user response：

I think you could do something like this:

SELECT 'compound' AS table_name, count_old, count_new, (count_old - count_new) AS diff FROM ( 
    SELECT( 
        (SELECT count(*) FROM (SELECT DISTINCT id FROM schema1.compound)) AS count_old,
        (SELECT count(*) FROM (SELECT DISTINCT id FROM schema2.compound)) AS count_new
    )

It was probably answered already, but it is a subquery/nested query.

CodePudding user response：

You can directly compute the COUNT on distinct values if you use the DISTINCT keyword inside your aggregation function. Then you can join the queries extracting your two needed values, and use them inside your query to get the output table.

WITH cte AS (
    SELECT new.cnt AS count_new,
           old.cnt AS count_old
    FROM       (SELECT COUNT(DISTINCT id) AS cnt FROM schema1.compound) AS old
    INNER JOIN (SELECT COUNT(DISTINCT id) AS cnt FROM schema2.compound) AS new
            ON 1 = 1
)
SELECT 'compound' AS table_name,
       count_new,
       count_old,
       count_new = count_old AS diff
FROM cte

CodePudding user response：

with counts as (
    select
        (select count(distinct id) from schema1.compound) as count_old,
        (select count(distinct id) from schema2.compound) as count_new
)
select
    'compound' as table_name,
    count_old,
    count_new,
    count_old - count_new as   diff
from counts;