Please help me to add a condition for the query so that it can calculate the unique months and years-CodePudding

Can you tell me where or how to write down the condition so that he counts the months for me from a certain client (redirect) and source (source)? I need to know how many invoices were issued, and this is counted by month, type January and February are 2 invoices, March April June 3 invoices, etc. I could write max instead of count, but this is not correct, since the client may appear in the middle of the year, for example in May, and he will have the values of the maximum month.

I hope I explained it clearly, if anything I will answer in the comments.

Here is my request:

select TA.redirect, 
  count(case when TA.source='zlat1' then extract(month from TA.date) else 0 end) number_of_accounts_zlat1,  
  count(case when TA.source='zlat2' then extract(month from TA.date) else 0 end) number_of_accounts_zlat2,  
  sum(TA.result_for_the_day) accrued
from total_accounts TA
group by TA.redirect

Here are tables and data query and result ---->

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0bc8002e59b03afedeac8d1b8dfc98d1

CodePudding user response：

insert into finace_base (redirect)
select distinct Ta.redirect /*this select will display those names that are not present
                              in FB if there is other info that u must add to insert then
                              just add , next to redirect and add whatever u like*/
from total_acounts TA
left join finace_base FB on TA.redirect=FB.redirect
where FB.redirect is null;
update finace_base FB
set zlat1=TA.zlat1,
    zlat2=TA.zlat2,
    accrued=TA.accrued
    from (select TA.redirect,
                 count(*) filter ( where TA.source='zlat1' ) as zlat1,
                 count(*) filter ( where TA.source='zlat2' ) as zlat2,
                 sum(TA.accrued) as accrued
          from(
                  select sum(TA.accrued) as accrued,
                         TA.date,
                         TA.redirect,
                         TA.source
                  from (select TA.result_for_the_day as accrued,
                               to_char(TA.date, 'yyyy-mm') as date,
                               TA.redirect,
                               TA.source
                        from total_accounts TA) TA
                  group by TA.redirect, TA.date, TA.source) TA
          group by TA.redirect) TA
    where FB.redirect=TA.redirect

i could not add it into comments cause it was too long essentialy you first run the insert into statement and then update it will only do inserts for redirects that are not added yet

CodePudding user response：

select TA.redirect,
   count(*) filter ( where TA.source='zlat1' ) as zlat1,
   count(*) filter ( where TA.source='zlat2' ) as zlat2,
   sum(TA.accrued)    
from(
select sum(TA.accrued) as accrued,
   TA.date,
   TA.redirect,
   TA.source
from (select TA.result_for_the_day as accrued,
         to_char(TA.date, 'yyyy-mm') as date,
         TA.redirect,
         TA.source
  from total_accounts TA) TA
group by TA.redirect, TA.date, TA.source) TA
 group by TA.redirect

there you go thats the answer. giving back to comunity that i have taken :D