SQL How to SUM with two table join

Issue

I have a problem with summing cells. I don’t know why, but when I try to sum up, I only add records from one table, even though the other also has records as in the photo below
enter image description here

This is code for expired and active :

proc sql;   
create table expired as
  select 
    policy_vintage
    ,count(NRB) as EXPIRED
  from PolisyEnd
  where CREDIT = "W" 
  group by policy_vintage
;
quit;
proc sql;   
create table active as
  select 
    policy_vintage
    ,count(NRB) as ACTIVE
  from PolisyEnd
  where CREDIT = "A"
  group by policy_vintage
;
quit;

And i would like create some seperate table with sum for each :

proc sql; 
create table graphbar as
select
sum(ak.ACTIVE) as ACTIVE
,sum(wy.EXPIRED) as EXPIRED
from active ak
left join expired wy
on wy.expired= ak.active
;
quit;

But i got some like that and i dont know why
enter image description here

Solution

CREATE TABLE expired (
  policy_vintage varchar(8) NOT NULL PRIMARY KEY,
  EXPIRED int NOT NULL
);

CREATE TABLE active (
  policy_vintage varchar(8) NOT NULL PRIMARY KEY,
  ACTIVE int NOT NULL
);

SELECT e.EXPIRED, a.ACTIVE
FROM (
  SELECT SUM(EXPIRED) AS EXPIRED FROM expired 
) AS e
  CROSS JOIN (
    SELECT SUM(ACTIVE) AS ACTIVE FROM active
  ) AS a

Answered By – Richard Barraclough

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published