How to add a condition for the query so that it can calculate the unique months and years in PostgreSQL

user_19634023 0
Viewed 41

How can I 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.

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

postgresql sql
2 Answers
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

user_194198030 
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

user_194198030
Related