Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: