Agrigate functions - help is needed, please!
kdwolfMar 30 2009 — edited Nov 3 2010WITH scheduled_events AS (
SELECT 1 AS sequence_no,
'Scheduled Avail' AS object_type,
30 AS duration_in_seconds
FROM dual
UNION ALL
SELECT 2 AS sequence_no,
'Scheduled Avail' AS object_type,
45 AS duration_in_seconds
FROM dual
UNION ALL
SELECT 3 AS sequence_no,
'Scheduled Programme' AS object_type,
1800 AS duration_in_seconds
FROM dual
UNION ALL
SELECT 4 AS sequence_no,
'Scheduled Avail' AS object_type,
30 AS duration_in_seconds
FROM dual
UNION ALL
SELECT 5 AS sequence_no,
'Scheduled Avail' AS object_type,
15 AS duration_in_seconds
FROM dual
UNION ALL
SELECT 6 AS sequence_no,
'Scheduled Avail' AS object_type,
60 AS duration_in_seconds
FROM dual)
SELECT * FROM scheduled_events;
I have a simple list of Events to be aired. Now I am required to supply their summary, grouped by Object Type, but in the follow way,
based on the above example:
the first two rows are of type Avail, so the first row of my group by should be
1 (first sequence_no), Scheduled Avail (object_type). 75 (total in seconds for the first two records)
the third row is of Schedule Programme, so it should be
3, Scheduled Programme, 1800
and the last three rows are of Schedule Avail again:
4,Scheduled Avail, 105
So the final result should be
1, Scheduled Avail, 75
3, Scheduled Programme, 1800
4,Scheduled Avail, 105
Is it possible?
Many thanks in advance!
Edited by: kdwolf on Mar 30, 2009 10:01 AM