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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top