Place in the same field where different value [closed]

Question

Place in the same field where different value [closed]

Navigation

#1 by (0 votes)

0

I have the following query:

(SELECT sno_hpersonalnomina.codnom, count(distinct(sno_hpersonalnomina.codper)), trunc(cast(sum(ABS(sno_hsalida.valsal)) as numeric), 2) as asigna
from sno_hpersonalnomina 
inner JOIN sno_hsalida ON sno_hpersonalnomina.codnom = sno_hsalida.codnom 
AND sno_hpersonalnomina.codper = sno_hsalida.codper 
and sno_hpersonalnomina.codperi= sno_hsalida.codperi
where sno_hpersonalnomina.codper::int= 116052 and sno_hpersonalnomina.codperi::int= 019 
and sno_hpersonalnomina.codnom::integer <= 8 and sno_hpersonalnomina.staper::int=1 and sno_hsalida.tipsal='A' 
group by sno_hpersonalnomina.codnom)

UNION all

(SELECT sno_hpersonalnomina.codnom, count(distinct(sno_hpersonalnomina.codper)), trunc(cast(sum(ABS(sno_hsalida.valsal)) as numeric), 2) as deducciones
from sno_hpersonalnomina 
inner JOIN sno_hsalida ON sno_hpersonalnomina.codnom = sno_hsalida.codnom 
AND sno_hpersonalnomina.codper = sno_hsalida.codper 
and sno_hpersonalnomina.codperi= sno_hsalida.codperi
where sno_hpersonalnomina.codper::int= 116052 and sno_hpersonalnomina.codperi::int= 019 
and sno_hpersonalnomina.codnom::integer <= 8 and sno_hpersonalnomina.staper::int=1 and (sno_hsalida.tipsal='D' or sno_hsalida.tipsal='P1') 
group by sno_hpersonalnomina.codnom)

Result

codnom  TRABAJADORES  ASIGNACIONES
-----------------------------------
0001        1           193397.27
0001        1           24831.02

But the problem is that the tipsal field has value A for assignments and for deductions has value P1 and D.

How do I translate this into the query?

This is the result of what is required

codnom  TRABAJADORES  ASIGNACIONES DEDUCCIONES TOTAL_NETO
---------------------------------------------------------      
 1        20         200000         100000      100000
 2        10         150000         50000       100000

sql postgresql

asked by Rafael Aguilar 26.12.2017 в 21:38

source

1 answer

Problem with Apache 2.4, Tomcat 7 and jk_mod How can I display the data of my mysql table in a TKinter List text in Python 2.7?

score 0 · Accepted Answer

It seems that in your example you are limiting yourself to a specific worker

where sno_hpersonalnomina.codper::int= 116052 
and sno_hpersonalnomina.codperi::int= 019 
and sno_hpersonalnomina.codnom::integer <= 8

Passing those clauses by default, and assuming that each worker has a unique codper , and that two or more workers can have the same codnom the query that you should do to arrive at a result like you want is:

SELECT codnom,
       count(DISTINCT codper) AS trabajadores,
       SUM(CASE WHEN sno_hsalida.tipsal='A' THEN ABS(sno_hsalida.valsal::numeric) ELSE 0 END) AS asignaciones,
       SUM(CASE WHEN sno_hsalida.tipsal='A' THEN 0 ELSE ABS(sno_hsalida.valsal::numeric) END) AS deducciones,
       SUM((CASE WHEN sno_hsalida.tipsal='A' THEN 1 ELSE -1 END)*ABS(sno_hsalida.valsal::numeric)) AS total_neto
FROM sno_hsalida
GROUP BY codnom

Again, this assuming that N workers can have the same codnom . Depending on the meaning of codper the number of workers may need to obtain it from COUNT(DISTINCT ...) of another field