SUM result value repeats even with case statement

Question

I'm using posgresql as a database and java as programming language with hibernate. My problem is this query:

select cast(sum(CASE WHEN p.nropack > 0 THEN p.nropack ELSE 0 END) as integer),
cast(sum(CASE WHEN p.nropack < 0 THEN p.nropack ELSE 0 END) as integer),
cast(p.fechareg as date) 
from pronostico p 
inner join aeropuerto a on (a.idaeropuerto=p.idaeropuerto)
inner join ciudad c on (a.idciudad=c.idciudad)
inner join pais ps on (ps.idpais=c.idpais)
inner join continente ct on (ct.idcontinente=ps.idcontinente)
where c.idciudad=105
group by cast (p.fechareg as date);

As a result I get:

sum;sum;fechareg 
30;-15;"2012-11-15"

But when I use it in my program:

public ArrayList<RepKardex> listarKardex(int ciud){  
    ciud=105; 
    ArrayList<RepKardex> listaKardex = new ArrayList<>();
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = session.beginTransaction();        
    String q = "select cast(sum( case when p.nropack > 0 then p.nropack ELSE 0 end ) as integer), "
            + "cast(sum( case when p.nropack < 0 then p.nropack ELSE 0 end ) as integer), "
            + "cast(p.fechareg as date) "
            + "from Pronostico p "
            + "inner join Aeropuerto a on (p.idaeropuerto = a.idaeropuerto) "
            + "inner join Ciudad c on (a.idciudad = c.idciudad) "
            + "inner join Pais ps on (c.idpais = ps.idpais) "
            + "inner join Continente ct on (ct.idcontinente = ps.idcontinente) "
            + "where c.idciudad = :ciud "
            + "group by cast(p.fechareg as date) ";                    
    Query query = session.createSQLQuery(q);        
    query.setInteger("ciud", ciud);
    List lista = query.list();        
    Iterator iter = lista.iterator(); 
    while (iter.hasNext()) {    
        Object[] row = (Object[]) iter.next();            
        if (row!=null){
            System.out.println("entrantes : "+(Integer)row[0]); 
            System.out.println("salientes : "+(Integer)row[1]); 
            RepKardex rep = new RepKardex((int)row[0],(int)row[1],(Date)row[2]);              
            listaKardex.add(rep);
        }

    }

    tx.commit();
    session.close();

    return listaKardex;
} 

It prints

entrantes: 30
salida:    30

Can someone help me figure out why it repeats the positive numbers even when I use the case statement inside the query? Thanks in advance.

Answer 1

You can simplify your query with LEAST and GREATEST:

SELECT sum(GREATEST(p.nropack, 0))::int AS entrantes
      ,sum(LEAST(p.nropack, 0))::int AS salida
      ,p.fechareg::date AS fechareg
from   pronostico  p 
JOIN   aeropuerto  a ON a.idaeropuerto = p.idaeropuerto
JOIN   ciudad      c ON c.idciudad = a.idciudad
JOIN   pais       ps ON ps.idpais = c.idpais
JOIN   continente ct ON ct.idcontinente = ps.idcontinente
where  c.idciudad = 105
GROUP  BY p.fechareg::date;

Other than that it looks just fine. I see no way the second column could return a positive number. Something else must go wrong here.

Edit:

Comment by @hvd helped to find that the client code seems to get confused by identical column names (both sum columns defaulted to "sum"). Explicit column aliases seem to fix this.