SQL how to count reocrds from date-CodePudding

I would like it to create a NUMBER column where the records for each date will be counted. So, for example, how many NRBs are there in 2021-10. However, when I choose count it gets such a result, sum cannot be because these are not numbers but an identification number Here is my result:

Here my code:

PROC SQL;   <- FIRST QUERY
create table PolisyEnd as 
    select distinct
    datepart(t1.data_danych) as DATA_DANYCH format yymmdd10.        
    ,(t4.spr_NRB) as NRB
    ,datepart(t1.PRP_END_DATE) as PRP_END_DATE format yymmdd10.
    ,datepart(t1.PRP_END_DATE) as POLICY_VINTAGE format yymmd7.,

case
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. < 0 THEN 'WYGASLA' 
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 0 and datepart(t1.PRP_END_DATE) - &gv_date_dly. <=7 THEN 'UWAGA' 
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 30 THEN 'AKTYWNA' 
    when datepart(t1.PRP_END_DATE) IS NULL THEN 'BRAK INFORMACJI O POLISIE' 

    end as POLISA_INFORMACJA

from 
    cmz.WMDTZDP_BH t1
left join 
        (select distinct kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth) t2 
on t2.obj_oid = t1.obj_oid

left join 
        (select distinct data_danych, kontr_id, kre_nrb from dm.BH_WMDTKRE_&thismonth) t3
on t3.kontr_id = t2.kontr_id
 
left join 
        (select distinct spr_NRB, spr_STATUS from _mart.mart_kred) t4
on t4.spr_NRB = t3.kre_nrb 


where datepart(t1.data_danych) between '5Aug2019'd and &gv_date_dly. and t1.Actual = "T"
and t4.spr_STATUS  ="A"


; SECOND CAME FROM FIRST
    create table PolisyEnd1 as
    select distinct
    DATE_
    ,(POLICY_VINTAGE)
    ,count(NRB) as NUMBER
    ,POLISA_INFORMACJA
    from PolisyEnd
    where INFORMATION ="U"
;
Quit;

EDIT 1 : I got the result, but how to do so that for 2021-11 there is one result and summed up all records for this period

CodePudding user response：

Rather than using a distinct here what you really want is a GROUP BY.

PROC SQL;   
    create table PolisyEnd1 as
    select
    DATE_
    ,(POLICY_VINTAGE)
    ,count(NRB) as NUMBER
    ,POLISA_INFORMACJA
    from PolisyEnd
    where INFORMATION ="U"
    group by DATE_, (POLICY_VINTAGE), POLISA_INFORMACJA
;
Quit;

CodePudding user response：

You can use group by If you want to count just based on the DATE_ column here is an example

select DATE_, count(NRB) as NUMBER
    from PolisyEnd
    where INFORMATION ="U"
group by DATE_

Otherwise, you can add other columns also in the group by and select clause.

For Edit1: For each month you can use this:

select POLICY_VINTAGE, SUM(NUMBER) as NUMBER
    from Your_Table
group by POLICY_VINTAGE