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.

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

Previous PostgreSQL Exercise: Filter Employees by Department.