Aggregation - counters

Other Tech Topics
1

I have a table T1 with below data
Domain;Mode;Channel;KPI;Value;AvgTm;Rounded_Time
USD;Manual;P1;consolidateUSD;20;2;11/14/2015 12:15:00
USD;Manual;P1;consolidateUSD;10;4;11/14/2015 12:30:00
USD;Manual;P1;consolidateUSD;10;2;11/14/2015 12:45:00
USD;Manual;P1;consolidateUSD;20;4;11/14/2015 13:00:00
DKT;Auto;P2;consolidateDKT;5;4;11/14/2015 12:15:00
DKT;Auto;P2;consolidateDKT;5;4;11/14/2015 12:30:00
DKT;Auto;P2;consolidateDKT;5;4;11/14/2015 12:45:00
DKT;Auto;P2;consolidateDKT;5;4;11/14/2015 13:00:00

I need to aggregate data based on "Rounded_Time" and I must get the SUM of "value" and AVG of "AvgTm" and pump into another table T2. This script will be scheduled hourly. So, next time, it must not pick earlier data.

output T2 table has
Domain;Mode;Channel;KPI;Value;AvgTm;Rounded_Time
USD;Manual;P1;consolidateUSD;60;3;11/14/2015 13:00
DKT;Auto;P2;consolidateDKT;5;4;11/14/2015 13:00

I am not able to get the output properl. I tried the below. I am new to SQL .Please help.

select * from T1
where Rounded_Time interval 60 and SUM(Value) and AVG(AvgTm)
group by Domain,Mode,Channel,KPI

2 
select Domain, Mode, Channel, KPI, SUM(Value) , AVG(AvgTm), MAX(Rounded_Time)
from   T1
where Rounded_Time >= convert(varchar(5), dateadd(hour, datediff(hour, 0, getdate()) - 1, 0), 108)
group by Domain, Mode, Channel, KPI
3

An average of an average? Not sure what value there is in that, but the below should do roughly what you want.

DECLARE @Test TABLE
(
Domain CHAR(3),
Mode VARCHAR(25),
Channel VARCHAR(25),
KPI VARCHAR(25),
Value INT,
AvgTm INT,
Rounded_Time TIME
)
INSERT INTO @Test
VALUES ('USD','Manual','P1','consolidateUSD',20,2,'12:15'),
('USD','Manual','P1','consolidateUSD',10,4,'12:30'),
('USD','Manual','P1','consolidateUSD',10,2,'12:45'),
('USD','Manual','P1','consolidateUSD',20,4,'13:00'),
('DKT','Auto','P2','consolidateDKT',5,4,'12:15'),
('DKT','Auto','P2','consolidateDKT',5,4,'12:30'),
('DKT','Auto','P2','consolidateDKT',5,4,'12:45'),
('DKT','Auto','P2','consolidateDKT',5,4,'13:00');

SELECT	T.Domain,
		T.Mode,
		T.Channel,
		T.KPI,
		Value = SUM(T.Value),
		AvgTM = AVG(T.AvgTM),
		Rounded_Time = CA1.AggTime
FROM	@Test AS T
CROSS
APPLY	(SELECT CAST(DATEADD(HOUR,DATEDIFF(HOUR,0,DATEADD(MINUTE,45,Rounded_Time)),0) AS TIME)) AS CA1(AggTime)
WHERE	CA1.AggTime >= CAST(DATEADD(HOUR,DATEDIFF(HOUR,0,DATEADD(MINUTE,45,GETDATE())),0) AS TIME)
GROUP	BY	T.Domain,
		T.Mode,
		T.Channel,
		T.KPI,
		CA1.AggTime;

if this runs every hour, I assume it also runs across days, how will you track the data once it is in a new day?

4

I am using Oracle SQL. Script is scheduled every hour. Hence, it must not pick up earlier data. Datatype of "Rounded_Time" is "Date". Can we use a flag and handle it for the next day?

5

Note that an average of averages over subsets is not the same as an average over the whole set.

6

Hi,
This code did not work. If I use CA1.AggTime in "GROUP BY" , my output is not correct.