Problem when ordering sql with orderby Date in postgresql

Question

Problem when ordering sql with orderby Date in postgresql

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have the following Query that results in joining the total sum of an account and the plots

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 the union, but when I use the union they end up coming cluttered, as for example 03/2018 comes in front of 04/2017. I am sending the date to to_char so I can put in the format I want and when I order it I use it in date format, the error is caused by the union, so how do I do to be sorted the date all this union set?

Query output

 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"

sql query postgresql select order-by

asked by anonymous 09.08.2017 / 01:25

3 answers

0

Try 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

09.08.2017 / 02:15

0

Try This

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;

In this way, it will convert date from text to date and will sort by taking into account month and year.

10.08.2017 / 16:10

Object.anonymous (Express + Middleware) TreeView with checkbox and AngularJS?

score 1 · Accepted Answer

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!