SUM problem

Transact-SQL
1

I have a problem when I am doing sums of two values from other tables. I only count one (active) even though it is also expired
obraz

proc sql;
create table policy_vintage_weekly1 as
select
policy_vintage
,count(NRB) as EXPIRED
from PolisyEnd
where POLISA_INFORMACJA = "W"
group by policy_vintage
;
quit;
proc sql;
create table policy_vintage_weekly2 as
select
policy_vintage
,count(NRB) as ACTIVE
from PolisyEnd
where POLISA_INFORMACJA = "A"
group by policy_vintage
;
quit;
proc sql;
create table graphbar as
select
sum(ak.ACTIVE) as ACTIVE
,sum(wy.EXPIRED) as EXPIRED
from policy_vintage_weekly2 ak
left join policy_vintage_weekly1 wy
on wy.policy_vintage = ak.policy_vintage
;
quit;

2

What dbms is that for? This is a (MS) SQL Server forum. Your code does not look like SQL Server.

3

hi

why do all this .. hope this helps

query the same table using case when

select
	   policy_vintage
	,  count(case when POLISA_INFORMACJA = "W" then NRB end) as EXPIRED
    ,  count(case when POLISA_INFORMACJA = "A" then NRB end) as ACTIVE
from 
    PolisyEnd
group by 
    policy_vintage