SQLTeam.com | Weblogs | Forums

GROUP BY related issue


#1

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;;


#2

You have > than which is the same in t2 11:00:00, really I'd expect no records try >=
Is the data type datetime for timestamp fields?


#3

it is date...


#4
CREATE TABLE #table1
(
Channel VARCHAR(20) NOT NULL,
Value INT NOT NULL,
Status INT NOT NULL,
Error_Code VARCHAR(20) NULL,
RND_Timestamp DATE NOT NULL,
Session_CD VARCHAR(20) NULL,
NAR VARCHAR(20) NULL
)

CREATE TABLE #table2
(
Channel VARCHAR(20) NOT NULL,
Value INT NOT NULL,
Status INT NOT NULL,
Error_Code VARCHAR(20) NULL,
Hour_Timestamp DATE NOT NULL,
Session_CD VARCHAR(20) NULL,
NAR VARCHAR(20) NULL
)


INSERT INTO #table1 VALUES
('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','','')


INSERT INTO #table2 VALUES
('USD',5,12,'','2-NOV-2015 11:00:00','','')

WITH > clause:

select channel,sum(value) Value,status,error_code,rnd_timestamp,session_cd,nar
from #table1
where rnd_timestamp>(select max(hour_timestamp) from #table2)
group by channel,status,error_code,rnd_timestamp,session_cd,nar

OUTPUT: 0

WITH >= clause

select channel,sum(value) Value,status,error_code,rnd_timestamp,session_cd,nar
from #table1
where rnd_timestamp>=(select max(hour_timestamp) from #table2)
group by channel,status,error_code,rnd_timestamp,session_cd,nar

OUTPUT: 1

USD 13 12 2015-11-02