Comparing counts from two tables based on a date column

Question

I have two tables with date columns in them. I want to count the number of rows grouped by that date column and see which dates don’t match the count of the other table + show the difference.

Ex. Table 1

Date	Count
Apr. 25	178048
Apr. 24	36273

Table 2

Date	Count
Apr. 25	178048
Apr. 24	36073

Wanted results:

Date	Count
Apr. 24	200

I tried to do minus query and union all but it doesn’t work.

SELECT date, count(*)
FROM table1
group by 1 order by 1 desc
MINUS
SELECT date, count(*)
FROM table2
group by 1 order by 1 desc
UNION ALL
(
SELECT date, count(*)
FROM table2
group by 1 order by 1 desc
MINUS
SELECT date, count(*)
FROM table1
group by 1 order by 1 desc
)

Please help a beginner out thank you

Answer 1

Try joining the summarised tables by date; using full outer join to allow for dates missing from one or the other:

select coalesce(Tbl1.dte, tbl2.dte) as dte
, coalesce(Tbl1.cnt,0)-coalesce(tbl2.cnt,0) as diff
from 
    (SELECT dte, count() cnt
    FROM table1 
    group by dte) Tbl1
    full outer join
    (SELECT dte, count() cnt
    FROM table2
    group by dte) Tbl2
    on tbl1.dte=tbl2.dte

I used dte, instead of date, because that maybe a reserved word in your dbms (you didn't tag it).