Question 114925 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0 
    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 😀

    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search