Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Quarterly Data Select using SQL

MLBrownJan 27 2012 — edited Jan 30 2012
All -

The database version I am using is 10.2.0.4.0

This is probably simple, but for the life of me, I cannot figure it out. I have a table of about 200 Codes and a table that contains millions of records that are date stamped (data is from 1995 to sysdate). I need a query for a view to be used in a report that will show the different codes used by employee's for specific quarters based on a date parameter. If they do not have data for that quarter I still want to show the quarter, but show 0 for the number.

I created this simple example. If I can see how someone does this, I can incorporate that logic into what I am doing.

SQL for test data:
create table bunch_of_codes
(id_code number(12) primary key,
description varchar2(10));

insert into bunch_of_codes
values (1, 'APPLE');

insert into bunch_of_codes
values (2, 'ORANGE');

insert into bunch_of_codes
values (3, 'GRAPE');

create table bunch_of_data
(id_code number(12),
activity_date date,
CONSTRAINT data_id_code
FOREIGN KEY (id_code) REFERENCES bunch_of_codes(id_code));

INSERT INTO bunch_of_data
VALUES (1, to_date('4/12/2011','MM/DD/YYYY'));

INSERT INTO bunch_of_data
VALUES (1, to_date('6/1/2011','MM/DD/YYYY'));

INSERT INTO bunch_of_data
VALUES (3, to_date('1/15/2011','MM/DD/YYYY'));

INSERT INTO bunch_of_data
VALUES (3, to_date('4/17/2011','MM/DD/YYYY'));

INSERT INTO bunch_of_data
VALUES (3, to_date('11/11/2011','MM/DD/YYYY'));

COMMIT;

What I want to see when I execute the SQL Statement is this:
DESCRIPTION QUARTER COUNT
----------- ------- -----
APPLE 1 0
APPLE 2 2
APPLE 3 0
APPLE 4 0
GRAPE 1 1
GRAPE 2 1
GRAPE 3 0
GRAPE 4 1
ORANGE 1 0
ORANGE 2 0
ORANGE 3 0
ORANGE 4 0

I need every code printed out regardless if it was used of not. If it wasn't used, I still want to see each quarter but show 0 for the count.

For this example, I can run this SQL to join the data:
select c.description,
to_char(d.activity_date,'Q') quarter,
count(d.id_code) count
from bunch_of_codes c,
bunch_of_data d
where c.id_code = d.id_code
group by c.description, to_char(d.activity_date,'Q')
order by description, quarter

But if there isn't any detail for the quarter then I do not get a row of data. I have tried all sorts of things, but nothing seems to work.

Please help if you have any ideas.
This post has been answered by Manguilibe KAO on Jan 28 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2012
Added on Jan 27 2012
6 comments
4,766 views