Select different conditions for the same column

Question

I have the following table with my current query.

Data        |  PesoTotal
01/04/2019  |  500         
02/04/2019  |  800     

And I would like to add a new column (Pesohorario), which would be the Peso on the date, but before 8 am.

I can do the consultations separately, but when I try to do it in a query, the values are distorted from reality.

CONSULTATION ONLY WITH THE PERSON

select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(hi.data, 'yyyy/MM/dd 
hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 
'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as data, 
sum(hi.Peso) as PesoHorario
from tabela hi where  
hi.data >= to_date('01/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and 
hi.data < to_date('03/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
TO_CHAR(hi.data,'HH24MI') BETWEEN '0000' AND '0759'
group by ...
order by ...  

CONSULTATION WITH THE EXTRA COLUMN

select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(hi.data, 'yyyy/MM/dd 
hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 
'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as data, 
sum(hi.Peso) as PesoTotal,
sum(ha.Peso) as PesoHorario
from tabela hi, tabela ha where  
hi.data >= to_date('01/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and 
hi.data < to_date('03/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
ha.data >= to_date('01/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and 
ha.data < to_date('03/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
TO_CHAR(ha.data,'HH24MI') BETWEEN '0000' AND '0759'
group by ...
order by ...

Answer 1

Use a CASE within the SUM function:

select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(hi.data, 'yyyy/MM/dd 
hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 
'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as data, 
sum(hi.Peso) as PesoTotal,
sum(CASE WHEN TO_CHAR(hi.data,'HH24MI') BETWEEN '0000' AND '0759' THEN hi.Peso ELSE 0 END) as PesoHorario
from tabela hi where  
hi.data >= to_date('01/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and 
hi.data < to_date('03/04/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
group by ...
order by ...