Problem sorting sql with orderby Date in postgresql

Question

I have the following query that results in the union of the total sum of an account and the installments

select 
    sum(total) as total, 
    datavencimento 
from  (
    select 
        sum (con_valoraserpago) as total,
        to_char(con_datavencimento,'mm/yyyy') as datavencimento 
    from conta  
    where con_formapagamento='V'  
    group by datavencimento  
    union
    select 
        sum (par_valorparcela) as total,
        to_char(par_datavencimento,'mm/yyyy') as datavencimento 
    from parcela
    where par_estorno <= 0  
    group by datavencimento
) AS A 
group by datavencimento 
order by datavencimento ASC NULLS FIRST;

If I run select by select they bring me sorted correctly by Union, but when I use Union they end up being disordered, as for example 03/2018 comes in front of 04/2017. i am sending the date to to_char to can put in the format I want and when I am ordering I use it in date format, the error is occasioned by the union, so as I do to be ordered the whole date set of union?

Output from the query

 total;datavencimento
    200;"01/2017"
    200;"02/2017"
    200;"02/2018"
    1534.24;"03/2017"
    200;"03/2018"
    450;"04/2017"
    50;"05/2017"
    650;"06/2017"
    2879.8;"07/2017"
    200;"08/2017"
    200;"09/2017"
    200;"10/2017"
    200;"12/2017"

Answer 1

This will work:

select 
    sum(total) as total, 
    datavencimento
from  (
    select 
        sum (con_valoraserpago) as total,
        to_char(con_datavencimento,'mm/yyyy') as datavencimento,
        **date_trunc('month', con_datavencimento) as data_order**
    from conta  
    where con_formapagamento='V'  
    group by datavencimento, **data_order**  
    union
    select 
        sum (par_valorparcela) as total,
        to_char(par_datavencimento,'mm/yyyy') as datavencimento,
        **date_trunc('month', par_datavencimento) as data_order**
    from parcela
    where par_estorno <= 0  
    group by datavencimento, **data_order**
) AS A 
group by datavencimento, **data_order**
order by **data_order** ASC NULLS FIRST;

See the test here!

Answer 2

Try it like this:

  select sum(total) as total, datavencimento 
        from  
        (
        SELECT sum(con_valoraserpago) as total,
            to_char(con_datavencimento,'mm/yyyy') as datavencimento ,
            to_char(con_datavencimento, "yyyy/mm") as data_order
        FROM conta  where con_formapagamento='V'  
        GROUP BY datavencimento  
        UNION
        SELECT sum(par_valorparcela) as total, 
                to_char(par_datavencimento,'mm/yyyy') as datavencimento ,
                to_char(par_datavencimento, 'yyyy/mm') as data_order
        FROM parcela  
        WHERE par_estorno <= 0  
        GROUP BY datavencimento
   ) 
   AS A group by datavencimento     
   order by data_order ASC NULLS FIRST

Answer 3

Try So

select 
       sum(total) as total, 
    datavencimento 
from  (
    select 
        sum (con_valoraserpago) as total,
        to_char(con_datavencimento,'mm/yyyy') as datavencimento 
    from conta  
    where con_formapagamento='V'  
    group by datavencimento  
    union
    select 
        sum (par_valorparcela) as total,
        to_char(par_datavencimento,'mm/yyyy') as datavencimento 
    from parcela
    where par_estorno <= 0  
    group by datavencimento
) AS A 
group by datavencimento 
order by TO_DATE(datavencimento,'mm/yyyy') ASC NULLS FIRST;

This way it will convert date from text to date and will sort taking into account month and year.