Generating a Report with PL/SQL in Oracle using GROUP BY and ORDER BY
Oracle Database Ideas
This tutorial was developed to assist members of my database team in my college class for Oracle database at DeVry University. Because this database is specific to the project assigned to my class, you obviously will not have access to the actual database. However, the techniques and methods used here are a good introduction to using PL/SQL for generating reports in Oracle.
In this report we are tasked with creating a list of each channel's name, number, and a count of the number of customers who picked that channel as a favorite channel.
When you look at this scenario the first thing you’ll notice is that this will be a GROUP BY situation. This is because one of the columns of information asked for is the COUNT of the number of customers who chose each channel as their favorite. COUNT is an aggregate function which usually results in grouping things together.
With that information, let’s start looking first for the columns we’ll need to display. They want the channel number, the channel name, and a column that displays a count of how many people chose each channel. In our ERD, we can identify these as follows.
SELECT chan_number, category_name, COUNT (chan_number)
Of course we don’t want the ugly names shown here, so we’ll use aliases to display better looking column names.
SELECT chan_number "Channel #", category_name "Category", COUNT (chan_number) "Customer's Chose As Favorite"
Now, since we are using data from more than one table we’ll need to JOIN these tables together as part of the FROM statement. In this case, both tables (Survey and Channel) are using the same PK/FK name to connect them (chan_number). So, we can use the JOIN USING statement.
FROM Channel JOIN Survey USING (chan_number)
This joins the two tables together through the chan_number attribute.
Putting It Together:
Next, we’ll need to GROUP all of the instances of chan_number together so that we only get one instance of each channel number while still calculating the COUNT of how many times each one appears in customers favorites.
GROUP BY chan_number, category_name
For some reason, it is necessary that both attributes above be included in the GROUP BY clause.
Finally, we want to put all this in some sort of organized order, so we use the ORDER BY statement.
ORDER BY chan_number;
This is what your output should look like. It doesn’t have to be exact as you are free to “pretty it up” any way you wish, but this shows the basic idea.
SELECT chan_number "Channel #", category_name "Category", COUNT(chan_number) "Customer's Chose As Favorite" FROM Channel JOIN Survey USING(chan_number) GROUP BY chan_number, category_name ORDER BY chan_number;
Hopefully this tutorial has been useful in helping you to understand the task of creating a simple report using PL/SQL in Oracle. Once you get your SQL statements put together test it on an installation of Oracle to make sure it works.