Q

Creating aggregate entries where no base data exists

I have a table with columns: "name", "date", "hour", "calls" (and other not interesting ones). I need to group the results by name, date and hour, summing calls. I also wish to have record, even if in the original table, there aren't such records. For example, if for a certain name1 and date1, I have 2 calls at 9, 4 calls at 11, 5 calls at 12, and so on, I wish to have this result set:

 
"name" "date" "hour" "calls"
name1   date1   9      2
name1   date1   10     0
name1   date1   11     4
name1   date1   12     5
...
Is there a solution?

There are several ways we can solve this problem. This time, I found a "helper" table useful. But first, here's our Calls table:

create table Calls
( Name varchar(10),
  CallDate date,
  CallHour numeric(2)
)
  
insert into Calls values ('Ada','01-JAN-2002',9);  
insert into Calls values ('Ada','01-JAN-2002',9);  
insert into Calls values ('Ada','01-JAN-2002',11);  
insert into Calls values ('Ada','01-JAN-2002',11);  
insert into Calls values ('Ada','01-JAN-2002',11);  
insert into Calls values ('Ada','01-JAN-2002',11);  
insert into Calls values ('Ada','01-JAN-2002',12);  
insert into Calls values ('Ada','01-JAN-2002',12);  
insert into Calls values ('Ada','01-JAN-2002',12);  
insert into Calls values ('Ada','01-JAN-2002',12);  
insert into Calls values ('Ada','01-JAN-2002',12);  
 
We'll need a table of hours (our helper table):
create table Hours
( Hour numeric(2) );

begin
insert into Hours values (0);
insert into Hours values (1);
insert into Hours values (2);
insert into Hours values (3);
insert into Hours values (4);
insert into Hours values (5);
insert into Hours values (6);
insert into Hours values (7);
insert into Hours values (8);
insert into Hours values (9);
insert into Hours values (10);
insert into Hours values (11);
insert into Hours values (12);
insert into Hours values (13);
insert into Hours values (14);
insert into Hours values (15);
insert into Hours values (16);
insert into Hours values (17);
insert into Hours values (18);
insert into Hours values (19);
insert into Hours values (20);
insert into Hours values (21);
insert into Hours values (22);
insert into Hours values (23);
commit;
end;
And here's our SQL with the results:
select Name, CallDate, Hour, 
       ( select count(*) from Calls t 
           where t.Name = c.Name 
             and t.CallDate = c.CallDate 
             and t.CallHour = h.Hour ) "Total Calls"
from (select distinct Name, CallDate from Calls) c, 
     Hours h


Name    CallDate       Hour   Total Calls
------- -------------- ------ -----------
Ada	1/1/2002	0	0
Ada	1/1/2002	1	0
Ada	1/1/2002	2	0
Ada	1/1/2002	3	0
Ada	1/1/2002	4	0
Ada	1/1/2002	5	0
Ada	1/1/2002	6	0
Ada	1/1/2002	7	0
Ada	1/1/2002	8	0
Ada	1/1/2002	9	2
Ada	1/1/2002	10	0
Ada	1/1/2002	11	4
Ada	1/1/2002	12	5
Ada	1/1/2002	13	0
Ada	1/1/2002	14	0
Ada	1/1/2002	15	0
Ada	1/1/2002	16	0
Ada	1/1/2002	17	0
Ada	1/1/2002	18	0
Ada	1/1/2002	19	0
Ada	1/1/2002	20	0
Ada	1/1/2002	21	0
Ada	1/1/2002	22	0
Ada	1/1/2002	23	0  

For More Information


This was first published in November 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close