Generate Regional Sales Summaries with a PostgreSQL Function

Aggregated Sales Data by Region

Write a PostgreSQL query to create a function that returns aggregated sales data grouped by region from the Sales table.

Solution:

-- Create or replace a function named get_sales_by_region that returns aggregated sales data by region
CREATE OR REPLACE FUNCTION get_sales_by_region() 
-- Specify that the function returns a table with columns region (TEXT) and total_sales (NUMERIC)
RETURNS TABLE(region TEXT, total_sales NUMERIC) AS $$
-- Begin the function block
BEGIN
    -- Return the result of the following query
    RETURN QUERY 
    -- Select the region and the sum of sale_amount from the Sales table
    SELECT region, SUM(sale_amount) 
    -- Specify the Sales table as the source of data
    FROM Sales 
    -- Group the results by region to aggregate sales per region
    GROUP BY region;
-- End the function block
END;
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• The goal is to generate a summary of sales totals for each region.
• This demonstrates the use of the RETURNS TABLE clause for returning multiple columns.
• Key Components:
• RETURNS TABLE(region TEXT, total_sales NUMERIC) : Defines the structure of the returned result set.
• GROUP BY region : Aggregates sales data by region.
• Real-World Application:
• Useful for generating regional sales reports and business intelligence analytics.

Notes:

• The Sales table must have region and sale_amount columns.
• Consider performance implications when aggregating large datasets.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function that returns aggregated sales data by product category within each region.
• Write a PostgreSQL function that returns monthly aggregated sales totals for each region over the last year.
• Write a PostgreSQL function that returns aggregated sales data by region, filtering out regions with sales below a certain threshold.
• Write a PostgreSQL function that returns aggregated sales data by region, including the average sale amount per region.

Go to:

• Practical Techniques for Returning Result Sets in PostgreSQL Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Filter Employees by Department.
NEXT : Return Orders Above a Threshold.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.