Compare data in two tables with the same schema in SQL-CodePudding

I have two tables,

Table A

SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableA` GROUP BY  DATE_FORMAT(date,'%m%y');

| amount   | date     |
| -------- | -------- |
| 11       | 05 22    |
| 22       | 06 22    |
| 33       | 07 22    |
| 44       | 08 22    |
| 55       | 09 22    |
| 66       | 10 22    |

Table B

SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableB` GROUP BY  DATE_FORMAT(date,'%m%y');

| amount   | date     |
| -------- | -------- |
| 77       | 07 22    |
| 88       | 08 22    |
| 99       | 09 22    |
| 111      | 10 22    |
| 222      | 11 22    |

Final output is like below by date, if one of the table doesn't not have the date, the amount will be 0.

| amount(Table A)| amount(Table B)| date  |
| -------------- | -------------- | ----- |
| 11             | 0              | 05 22 | ← 
| 22             | 0              | 06 22 | ←
| 33             | 77             | 07 22 |
| 44             | 88             | 08 22 |
| 55             | 99             | 09 22 |
| 66             | 111            | 10 22 | 
| 0              | 222            | 11 22 | ←

Table A and Table B are the results of grouped by date to sum amounts.

CodePudding user response：

Use full outer join on and coalesce function to default amount to 0 if not exists.


-- 1. Create tables
create table table_a
(
    mo_yr  varchar(5),
    amount double
);

create table table_b
(
    mo_yr  varchar(5),
    amount double
);

-- 2. Insert data
insert into table_a
values
('05 22', 11),
('06 22', 22),
('07 22', 33),
('08 22', 44),
('09 22', 55),
('10 22', 66);


insert into table_b
values
('07 22', 22),
('08 22', 22),
('09 22', 22),
('10 22', 22),
('11 22', 22);


-- 3. SQL query
select COALESCE(a.amount ,0)      as amount_a,
       COALESCE(b.amount, 0)      as amount_b,
       COALESCE(a.mo_yr, b.mo_yr) as yr_mo
  from table_a a
  full
  join table_b b
 using (mo_yr)
 order by mo_yr;

Result:

amount_a|amount_b|yr_mo|
-------- -------- ----- 
    11.0|     0.0|05 22|
    22.0|     0.0|06 22|
    33.0|    22.0|07 22|
    44.0|    22.0|08 22|
    55.0|    22.0|09 22|
    66.0|    22.0|10 22|
     0.0|    22.0|11 22|

EDIT: Another is UNION ALL and then GROUP BY as below:

with cte as (
select mo_yr, amount as amount_a, 0 as amount_b from table_a
union all
select mo_yr, 0 as amount_a, amount as amount_b from table_b)
select sum(amount_a) as amount_a,
       sum(amount_b) as amount_b,
       mo_yr
  from cte
 group by mo_yr
 order by mo_yr;