Examples of PARTITION BY and GROUP BY in PL/SQL

Set up an environment:

CREATE TABLE sales_data (
product VARCHAR(50),
category VARCHAR(50),
sales_amount DECIMAL(10, 2)
);
INSERT INTO SALES_DATA (PRODUCT, CATEGORY, SALES_AMOUNT)
VALUES
('Product A', 'Electronics', 100.00),
('Product B', 'Electronics', 150.00),
('Product C', 'Clothing', 75.00),
('Product D', 'Clothing', 120.00),
('Product E', 'Electronics', 200.00);

Output:

Examples of PARTITION BY

Example 1:

Calculate the average sales amount per category while preserving all rows.

SELECT product, category, sales_amount,
AVG (sales_amount) OVER (PARTITION BY category) AS avg_sales
FROM sales_data;

Output:

Explanation: On this query, the PARTITION BY clause is used within the OVER clause of the AVG function. It separately deals with the data by the category column and then AVG function computes the average sales amount for each partition (category). Different from GROUP BY, each of all rows is retained in the results set with the average sales amount also displayed together with other details..

Example 2:

Rank products within each category based on their sales amount:

SELECT product, category, sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS sales_rank
FROM sales_data;

Output:

Explanation: Therefore, the PARTITION BY clause is part of the RANK function’s OVER clause as seen in the following input. It allocates data into groups assigned by the category column, and then the RANK function gives a rank to every row of its corresponding division, based on the sales_amount which pertains to the highest sales amount being assigned the rank 1. This thus enables else-wise categorically unrankable products to be compared against one another.

Examples of GROUP BY

Example 1:

Determine the total Sales Amount for each category.

SELECT category, sales_amount AS total_sales.
FROM sales_data
GROUP BY category;

Output:

Explanation: In this query, the GROUP BY clause is used to group rows of the sales_data table by the category column. Then, the SUM function is applied to the sales_amount column within each group, calculating the total sales amount for each category. Here GROUP BY function is applied on to the sales data table with the category as the summary column’s criteria. Next, we perform the SUM function on the sales_amount column of each group to total up the sales amounts for each category.

Example 2:

Count the number of products in each category:

SELECT category, product_count(product) AS product_count.
FROM sales_data
GROUP BY category;

Output:

Explanation: This query in the end uses the GROUP BY clause to group the necessary rows by category column. COUNT function is applied to the product column within each division, calculating how many products a category have.

Effective data manipulation during SQL queries especially while using PL/SQL is critical for obtaining the highest possible performance on database operations. We will mostly use two clauses for achieving these tasks and they are PARTITION BY and GROUP BY.

Although they both are meant for data organization, however, they work in a completely different manner and also can be useful in different scenarios. Such dissimilarities have to be studied if you would like to get relevant and meaningful answers.