I am trying to perform aggregation on a table. But it is not aggregating properly for some cases. Please find the below input.
table t1.
CHANNEL;VALUE;STATUS;ERROR_CODE;RND_TIMESTAMP;SESSION_CD;NAR;
USD;4;12;;2-NOV-2015 11:00:00;;
USD;4;12;;2-NOV-2015 11:00:00;;
USD;2;12;;2-NOV-2015 11:00:00;;
USD;3;12;;2-NOV-2015 11:00:00;;
Output table t2
CHANNEL;VALUE;STATUS;ERROR_CODE;HOUR_TIMESTAMP;SESSION_CD;NAR;
USD;5;12;;2-NOV-2015 11:00:00;;
QUERY
select channel,sum(value),status,error_code,rnd_timestamp,session_cd,nar
from t1
where rnd_timestamp>(select max(hour_timestamp) from t2)
group by channel,status,error_code,rnd_timestamp,session_cd,nar
Why is it not considering the other 2 rows for aggregation. Is it because some fields in group by have null ? . How to solve this issue?
output must be : USD;13;12;;2-NOV-2015 11:00:00;;