Generating a Report with PL/SQL in Oracle using GROUP BY and ORDER BY

Oracle Database Ideas

Source

Introduction

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.

Getting Started:

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"

ERD Elements for Report
ERD Elements for Report | Source

Making Connection

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.

Organizing:

Finally, we want to put all this in some sort of organized order, so we use the ORDER BY statement.

ORDER BY chan_number;

Output:

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.

Oracle Report Output showing Channel, Category, and Customer's Favorite
Oracle Report Output showing Channel, Category, and Customer's Favorite | Source

Full Code:

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;


Conclusion:

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.

More by this Author


Comments 1 comment

Cyrille MODIANO profile image

Cyrille MODIANO 2 years ago from Montreal

Thanks for the share, it is a great tutorial for beginners. However this juste simple SQL not PL/SQL. Keep up the good work :)

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working